Apex Code Development You need to sign in to do that
Don't have an account?
Mercury Holidays Force different collation on SOQL Order By?
I'm currently using a SOQL Object Query as part of an SSIS package and I need the data sorted for the next step in my SSIS data flow.
However, SOQLs "Order by" does not produce the sort order that SSIS expects and uses internally. For example, the list:
aa
ab
a-b
ac
Comes out like this with a SOQL Order By, which uses ASCII sorting:
a-b
aa
ab
ac
But SSIS is expecting the list like this, as it uses Windows Collation:
aa
a-b
ab
ac
Is there any way to force the SOQL Order By to use Windows Collation instead of its default? Is there any workaround in SOQL to get the list sorted how I want?
And yes I know I can use SSIS Sort Transformations, but I'm trying to avoid them because they're clunky and expensive. I can use them as a last resort, but I'd prefer the data sorted at source.
However, SOQLs "Order by" does not produce the sort order that SSIS expects and uses internally. For example, the list:
aa
ab
a-b
ac
Comes out like this with a SOQL Order By, which uses ASCII sorting:
a-b
aa
ab
ac
But SSIS is expecting the list like this, as it uses Windows Collation:
aa
a-b
ab
ac
Is there any way to force the SOQL Order By to use Windows Collation instead of its default? Is there any workaround in SOQL to get the list sorted how I want?
And yes I know I can use SSIS Sort Transformations, but I'm trying to avoid them because they're clunky and expensive. I can use them as a last resort, but I'd prefer the data sorted at source.
You may have to use SSIS Sort Transformation.