• 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

    June 22, 2005 Hey, Ted

    Thanks for explaining how to ignore case when sorting and selecting records with SQL. Can you tell me how to do the same with Query/400?

    –Ron

    Sure. First, here’s a database file that we can query.

    LASTNAME    FIRSTNAME 
    smith       billy     
    Smith       Amos      
    SMITH       CHARLEY   
    smith       DICK      
    Smith       ELMO      
    SMITH       dan       
    JONES       Andy
    

    The normal sort places lowercase letters before uppercase ones, like this:

    LASTNAME    FIRSTNAME 
    smith       billy     
    smith       DICK      
    JONES       Andy      
    Smith       Amos      
    Smith       ELMO      
    SMITH       dan       
    SMITH       CHARLEY
    

    From the Define the Query panel, place a 1 (one) beside the Select collating sequence option and press Enter. If your system has a collating sequence option of 2 (Query for iSeries English), you can use this option. If not, select option 5 (System sort sequence). On the following panel, Select System Sort Sequence, select Sort sequence 3 (Shared) and a Language id of *JOBRUN. Our example data sorts alphabetically.

    LASTNAME    FIRSTNAME 
    JONES       Andy      
    Smith       Amos      
    smith       billy     
    SMITH       CHARLEY   
    SMITH       dan       
    smith       DICK      
    Smith       ELMO
    

    You will also be able to select data regardless of case. Here’s an example from the Select Records panel.

    Field             Test   Value 
    FIRSTNAME         LIKE   'D%'
    

    All records in the resulting dataset have first names that begin with either capital or lowercase D.

    LASTNAME    FIRSTNAME
    SMITH       dan      
    smith       DICK
    

    The system would have given me the same results if I had coded a lowercase d in the Value expression.

    Field             Test   Value 
    FIRSTNAME         LIKE   'd%'
    

    –Ted

    RELATED STORY

    Case-Insensitive Sorting and Record Selection in SQL/400


    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    LaserVault Boosts Compliance Efforts with New Audit Log Original Looks for Performance Problems with New TestLOAD Tool

    Leave a Reply Cancel reply

Volume 5, Number 25 -- June 22, 2005
THIS ISSUE
SPONSORED BY:

Advanced Systems Concepts
WorksRight Software
Patrick Townsend & Associates

Table of Contents

  • Execute SQL Statements on DB2 UDB for Windows from the iSeries
  • Case-Insensitive Sorting and Record Selection with Query/400
  • Turning Off ODBC Journaling Is Not a Good Idea

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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