Re: ORDER BY and alternate collating sequences

Joe

I've used *LANGIDSHR in some of our code - created extra indexes to support it. Then used a SET OPTION in the code to make the thing do this.

My memory may not be linear anymore (someone else's line!) but I seem to see (NOTE - SEEM to see, for the a n a l - ytical among us) a kind of ASCII sort order just by doing this - at least, numbers come before letters.

I've not fully tested this - say, by using all the possible values like punctuation and accented characters, to see what happens - I've only noticed the number thing - and, of course, that little-a and big-a show up together. And maybe even the accented A's are grouped with the unaccented - it's been a while since I really looked closely.

HTH
Vern

On 3/27/2012 7:57 AM, Joe Pluta wrote:

One thing I'm running into more and more as I integrate IBM i back ends
with the rest of the world is sort sequences. Upper and lower is one
such instance although you can get around that with UPPER at the cost of
some performance. A stickier issue is EBCDIC vs ASCII collating, and of
course specifically digits and non-digits (although punctuation can be a
problem as well).

I'd like to start with simply doing a select with an order by in STRSQL
and get records in, say, a case-insensitive ASCII sequence. I'd then
like to be able to execute that same query via a traditional ODBC call.
However, I'm not sure how to get there. The closest I've seen is a note
on creating an SQL INDEX while the session is set to
SRTSEQ(*LANGIDSHR). THis seems to be more for case sensitivity than
ASCII, but I'm willing to learn. Has anyone done this?

Joe