• 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:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Tags:

    Sponsored by
    ARCAD Software

    AI is opening new possibilities for IBM i modernization, but for business-critical applications, performance alone is not enough: managing risks remains essential.

    How do BOB, Claude, and ARCAD Transformer RPG compare in converting RPG code to free-form RPG?

    Join Alan Ashley, Sr. Solutions Architect, as he presents a hands-on benchmark comparing these three solutions.

    Beyond conversion performance, discover how to minimize modernization risks through automated regression testing, validating application behavior and detecting unintended changes before they impact business operations.

    During this session, you’ll discover:

    • Compare code conversion performance: BOB vs. Claude vs. ARCAD Transformer RPG
    • Understand the role of deterministic automation alongside AI in a comprehensive IBM i modernization strategy
    • Accelerate RPG code transformation with predictable, rule-based processes
    • Reduce business risks through automated regression testing with ARCAD Verifier
    • Build a controlled, auditable modernization process that protects business-critical applications

    Register now for this webinar to discover how ARCAD Software’s deterministic modernization solutions complement AI initiatives, providing a structured, repeatable, and controlled approach to application transformation.

    Share this:

    • Share on Reddit (Opens in new window) Reddit
    • Share on Facebook (Opens in new window) Facebook
    • Share on LinkedIn (Opens in new window) LinkedIn
    • Share on X (Opens in new window) X
    • Email a link to a friend (Opens in new window) Email

    Execute SQL Statements on DB2 UDB for Windows from the iSeries Turning Off ODBC Journaling Is Not a Good Idea

    Leave a ReplyCancel 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 i Delivers New Development Tooling With 2026 Fall Tech Refreshes
  • Big Blue Says AI Is Not Behind The Big Uptick In IBM i Security Alerts
  • Guru: Web Service With Pagination
  • The GenAI Revolution And Product Shortages Send IT Budgets Skyward
  • IBM i PTF Guide, Volume 28, Number 40
  • IBM i PTF Guide, Volume 28, Number 41
  • Bob Code Assistant Gets Better Understanding Of IBM i Applications
  • IBM i In The Cloud And The Case For Reversing Replication
  • Guru: From Hiking Boots To Long-Term Memory
  • Inside IBM’s New PowerHA Cloud Offering, Full System Manager

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