• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Unusual But Logical SQL Sorting

    September 13, 2006 Hey, Ted

    A physical file that I’ve loaded with data from another system has a department field that I would like to use for sorting. The department field contains a description, rather than a code. I need to sort the data on the description, but not in alphabetical order. That is, I want a certain department to appear first, then a certain department to appear next, and so on. The only way I know to pull this off is to create a little table that lists the departments and the order each one appears in the sort, but I do not like to clutter up the database with little one-use files. Do you have another idea?

    –Ross

    Yes, I do. Let’s assume there are three departments–Accounting, Shipping, and Receiving–and you want to sort the data in that order. Alphabetical sorting will put Receiving ahead of Shipping, so that’s out.

    Give the LOCATE function a try. In the first parameter, specify the name of the sort field. (I’ll assume it’s DEPT for this example.) The second parameter should contain a list of the departments. If the department field is fixed-length, be sure to pad each department name in the list-including the last one-with trailing blanks. Here’s an example:

    select * from mydata                                          
     order by locate(dept, 'Accounting  Shipping    Receiving   ')
    

    If you prefer, you can also use the POSITION and POSSTR functions.

    select * from mydata                                              
     order by posstr(dept, 'Accounting  Shipping    Receiving   ')    
    select * from mydata                                              
     order by position(dept in 'Accounting  Shipping    Receiving   ')
    

    If DEPT is a variable-length field, you don’t have to pad with trailing blanks.

    select * from mydata2                                 
     order by posstr(dept, 'AccountingShippingReceiving')
    

    Or you can avoid the trailing blanks by dropping trailing blanks from the search argument.

    select * from mydata                                      
    order by locate(trim(dept), 'AccountingShippingReceiving')
    

    Be aware that these functions return zero if the search argument is not in the list. Therefore, records for any departments you omit from the list will sort at the top of the returned data.

    –Ted

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    MKS:  Application lifecycle management solutions
    California Software:  Migrate iSeries apps to Windows, Linux, or Unix
    COMMON:  Join us at the Spring 2007 conference, April 29 – May 3, in Anaheim, California

    Avnet Creates Training Portal for Resellers of IBM Wares Redirecting a List of Qshell Commands

    Leave a Reply Cancel reply

Volume 6, Number 33 -- September 13, 2006
THIS ISSUE SPONSORED BY:

WorksRight Software
iTera
Patrick Townsend & Associates

Table of Contents

  • Developers Don’t Despair: Virtualize Your PC
  • Unusual But Logical SQL Sorting
  • Admin Alert: One Common Cure for SQL0901 Package Errors

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