sponsored links

Collapse

Announcement

Collapse
No announcement yet.

Order By on Char field - numbers before letters?

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Order By on Char field - numbers before letters?

    When you order by a character field, letters are sorted before numbers.
    E.g.
    'AZA'
    'A9A'
    'AAA'
    'A0A'
    Sorts ascending to:
    'AAA'
    'AZA'
    'A0A'
    'A9A'

    Is there any way to make SQL sort numbers before letters? So I would get this instead:
    'A0A'
    'A9A'
    'AAA'
    'AZA'

    Note that I care about numerics anywhere in the field (which is a varchar 32)

  • #2
    Try this- First, CHGJOB SRTSEQ(*LANGIDSHR).Then change the sort sequence for your SQL to *JOBRUN. Note that you'll need to do this for each run of this job, and it will affect other components of your job as well.

    You could also create your own sort sequence, but that can be a real pain in the a... ummm... neck.

    Comment


    • #3
      Not sure about SQL but I know for RPG when you do this you have to be cautious because it may affect more than the sort sequence. For instance with RPG it also affects _all_ comparisons, and that includes lookups etc.

      Comment


      • #4
        If you're doing this as embedded SQL in RPG:

        At the start of your code you should have SET OPTION - one of the options is SRTSEQ = *LANGIDSHR and it will do what you want.

        Here is what I have:

        Code:
        EXEC SQL                              
          SET OPTION NAMING = *SYS,           
                     CLOSQLCSR = *ENDMOD,     
                     COMMIT = *NONE,          
                     SRTSEQ = *LANGIDSHR,     
                     DATFMT = *ISO;
        If you're doing it using STRSQL - do an F12 - 1 (Change session attributes) page down and change Sort Sequence to *LANGIDSHR

        If you're using a different means than above, look how to set the sort sequence for the connection.

        Comment

        sponsored links

        Collapse

        Working...
        X