• 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
    DRV Tech

    Get More Out of Your IBM i

    With soaring costs, operational data is more critical than ever. IBM shops need faster, easier ways to distribute IBM applications-based data to users more efficiently, no matter where they are.

    The Problem:

    For Users, IBM Data Can Be Difficult to Get To

    IBM Applications generate reports as spooled files, originally designed to be printed. Often those reports are packed together with so much data it makes them difficult to read. Add to that hardcopy is a pain to distribute. User-friendly formats like Excel and PDF are better, offering sorting, searching, and easy portability but getting IBM reports into these formats can be tricky without the right tools.

    The Solution:

    IBM i Reports can easily be converted to easy to read and share formats like Excel and PDF and Delivered by Email

    Converting IBM i, iSeries, and AS400 reports into Excel and PDF is now a lot easier with SpoolFlex software by DRV Tech.  If you or your users are still doing this manually, think how much time is wasted dragging and reformatting to make a report readable. How much time would be saved if they were automatically formatted correctly and delivered to one or multiple recipients.

    SpoolFlex converts spooled files to Excel and PDF, automatically emailing them, and saving copies to network shared folders. SpoolFlex converts complex reports to Excel, removing unwanted headers, splitting large reports out for individual recipients, and delivering to users whether they are at the office or working from home.

    Watch our 2-minute video and see DRV’s powerful SpoolFlex software can solve your file conversion challenges.

    Watch Video

    DRV Tech

    www.drvtech.com

    866.378.3366

    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

  • 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
  • Big Blue Raises IBM i License Transfer Fees, Other Prices
  • Keep The IBM i Youth Movement Going With More Training, Better Tools
  • Remain Begins Migrating DevOps Tools To VS Code
  • IBM Readies LTO-10 Tape Drives And Libraries
  • IBM i PTF Guide, Volume 27, Number 23

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