Re: Using Alternate Sequence In SQL - QCASE256

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

All examples that use UPPER() function in the ORDER BY might work well 
enough, but if you had the function in a WHERE clause, it'd tend toward 
a table scan most of the time.

As you are in embedded SQL, you can use the SET OPTION 
SRTSEQ(*LANDIDSHR) - this is a compile-time only setting - you can't 
condition it at run-time. The setting is honored at the module level, so 
far as I know.

Understand also that if you use this option, up to V5R3 and maybe V5R4, 
the query will be optimized by CQE unconditionally - it's one of those 
things that is a make-or-break for SQE. This is true, even when you have 
indexes or LFs that are shared-weight. But CQE is not necessarily slow 
in this case, especially when you have appropriate indexes.

Also know that the sort order is not going to look like EBCDIC - numbers 
precede letters, for example. Do a WRKTBL QLA10025S to see the order of 
things. (0025 is hex for 37 - I'm guessing that LA1 stands for Latin I).

I made a file with a single character field - inserted records with 
physical order A, a, and 1 (one). Running
SELECT * FROM TESTLANGID ORDER BY CHAR1
under different sort sequences got these results -

*HEX - a A 1
*LANGIDUNQ - 1 a A
*LANGIDSHR - 1 A a

Sorting based on LANGID has numbers before letters. Shared probably 
honors the FIFO LIFO thing on the column in question - have not verified 
that. Here it's in arrival sequence for code points with equal weight.

HTH
Vern

darren@xxxxxxxxx wrote:
> The most obvious choice would be the following:
>
>
>          Exec Sql Declare cursorJ77 CURSOR For
>               Select   *
>               From     File77
>               order by AgcmDATE, UPPER(Name)
>               For Read Only;
>
>          Exec Sql Open  cursorJ77;
>
>
>
>