• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Case-Insensitive Sorting and Record Selection with Query/400, Take Two

    July 20, 2005 Dear Colleagues

    Alert and astute reader Sarah wrote in response to the article, “Case-Insensitive Sorting and Record Selection with Query/400,” that the two sorting options I mentioned are not equivalent. After checking it out, I learned she’s right.

    In the EBCDIC coding sequence, lowercase letters precede uppercase letters, which precede numeric digits. Punctuation and special characters are scattered throughout the sequence. See Chapter 9 of the V5R2 Query manual for more information. Sorting a file of item information by item class yields output of the following nature:

    ITCLS  ITNBR     ITDSC         
     k8    A1120     WHOZIT NOZZLE 
     BU    D881      4" FLAPPITER  
     B1    D880      3" FLAPPITER  
     B1    D882      5" FLAPPITER  
     K4    A1119     WHOZIT        
     K7    A1121     WHOZIT HOSE   
     2A    A101      WIDGET        
     2C    A103      WIDGET MOUNT  
     25    A102      WIDGET HOLDER 
     36    A104      WIDGET BRACKET
    

    Notice that the lowercase k precedes the uppercase B.

    Selecting collating sequence option 2 causes English-language systems that work from CCSID 37 to sort in more or less the same sequence, except that uppercase and lowercase letters are weighted equally.

    ITCLS  ITNBR     ITDSC          
     BU    D881      4" FLAPPITER   
     B1    D880      3" FLAPPITER   
     B1    D882      5" FLAPPITER   
     K4    A1119     WHOZIT         
     K7    A1121     WHOZIT HOSE    
     k8    A1120     WHOZIT NOZZLE  
     2A    A101      WIDGET         
     2C    A103      WIDGET MOUNT   
     25    A102      WIDGET HOLDER  
     36    A104      WIDGET BRACKET
    

    Sarah pointed out that option 5, System sort sequence, places digits ahead of letters. It also equates letters that have diacritical marks with their unembellished counterparts. Here’s the same sort using shared weights under CCSID 37.

    ITCLS  ITNBR     ITDSC          
     25    A102      WIDGET HOLDER  
     2A    A101      WIDGET         
     2C    A103      WIDGET MOUNT   
     36    A104      WIDGET BRACKET 
     B1    D880      3" FLAPPITER   
     B1    D882      5" FLAPPITER   
     BU    D881      4" FLAPPITER   
     K4    A1119     WHOZIT         
     K7    A1121     WHOZIT HOSE    
     k8    A1120     WHOZIT NOZZLE
    

    This sequence is not exactly like ASCII, but it may be close enough for those who need to sort in an ASCII-compatible sequence.

    It may also be worth mentioning that you can define a sort sequence of your own to be used in a query. From the Select collating sequence panel, choose option 3 (Define the sequence). Query/400 presents a screen into which you can key your weights of choice. The initial weights are taken from the national language sequence used on your system.

    –Ted


    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Eradani

    API Enable Your IBM i Today

    Create & Manage Secure, High Speed, APIs for IBM i with Eradani Connect!

    o Automatically schedule and track shipments

    o Communicate with IoT devices

    o Connect to Amazon, Shopify and others

    o Automate order processing

    o Create mobile and web user interfaces

    o Generate Open API Swagger Documentation

    Eradani Mentors will ensure your success!

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Quadrant Updates Fax Server Software SafeData Launches Hosting Service for HA and DR

    Leave a Reply Cancel reply

Volume 5, Number 28 -- July 20, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
Advanced Systems Concepts
Bug Busters Software Engineering

Table of Contents

  • API Corner: Using the User Profile Exit Programs
  • Case-Insensitive Sorting and Record Selection with Query/400, Take Two
  • Admin Alert: Three Keys to Better ODBC Library List Management

Content archive

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

Recent Posts

  • The Cloud’s Future Is So Bright, So Why Are You So Glum?
  • Most App Modernization Projects a Struggle, Survey Finds
  • COMMON Launches IBM i Security Conference
  • Four Hundred Monitor, August 17
  • A Slew Of Add-On Services For Power10 Systems
  • Power10 Midrange Machine: The Power E1050
  • IBM Puts The Finishing Touches On PowerHA For IBM i 7.5
  • Guru: Regular Expressions, Part 2
  • Get Your Security Education, And Not From The School Of Hard Knocks
  • IBM i PTF Guide, Volume 24, Number 33

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.