• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • More Conditional Sorting with SQL

    November 10, 2004 Hey, Ted

    Your tip “Conditional Sorting with SQL” is one cool tip! May I add two more examples?

    My examples are in RPG, but I assume they will work with any language. In both examples, I use host variables to control the sort.

    Host variable SortOption controls the sort in the first example. If host variable SortOption has a value of 1, the data is retrieved in order by customer name. If two customers have the same name, they are retrieved in customer number sequence. If SortOption has any other value, the data is retrieved in customer number sequence.

    D SortOption      S              1P 0
    
    C/exec sql
    C+
    C+   Declare C1 Cursor for
    C+   select cusnum, lstnam, init
    C+   from qiws/qcustcdt
    C+   order by
    C+      case when :SortOption = 1
    C+             then lstnam
    C+             else digits(cusnum) end,
    C+      case when :SortOption = 1
    C+             then cusnum
    C+             else 0 end
    C+
    C/end-exec
    

    In the second example, the data may be sorted by state, in either ascending or descending sequence. If host variable SortSeq has a value of A, the data is returned in ascending order by state. Within state, the data is sorted by last name and initials. A SortSeq value of D causes the states to be sorted in descending order, but the last name and initial fields are sorted in ascending order. If SortSeq has some other value, state is not used for sorting.

    D SortSeq         S              1A
    
    C/exec sql
    C+
    C+   Declare C1 Cursor for
    C+   select state, lstnam, init from qiws/qcustcdt
    C+   order by
    C+      case when :SortSeq = 'A' then state else ' ' end,
    C+      case when :SortSeq = 'D' then state else ' ' end desc,
    C+      lstnam,
    C+      init
    C+
    C/end-exec
    

    –John

    Thanks for the ideas, John. Several readers wrote in to say that they liked the tip, but none of them sent examples.


    Using host variables to affect the behavior of SQL commands is one way to add flexibility to an application. For more information, see these articles from previous editions of this newsletter.

    “Alternate SQL Row-Selection Criteria”

    “Alternate SQL Row-Selection Criteria, Take 2”

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Raz-Lee Security

    Start your Road to Zero Trust!

    Firewall Network security, controlling Exit Points, Open DB’s and SSH. Rule Wizards and graphical BI.

    Request Demo

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Symantec Adds Regulatory Compliance to Security Management Tool How the i5s Compare with Other Big Boxes

    Leave a Reply Cancel reply

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle