• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Common Sense Comparisons

    May 17, 2006 Hey, Esteemed, Professional Colleagues

    Today’s technical tip is not rocket science, but that’s OK with me because rocket science won’t help me keep the factory going. No, today’s tip is a practical, yet very simple, SQL (and OPNQRYF) technique that I hope to use regularly for years to come.

    One of the first things that was drilled into my bald head in my early days of computer programming was that you can’t compare numeric data to character data. It didn’t matter what the language was–RPG II, COBOL 68, FORTRAN IV, GW-BASIC–numeric and character were apples and oranges.

    I never questioned this restriction until I started working in a production environment. That’s when I found out that a certain datum–a date, an item number, or a department code, for instance–might be stored in a numeric field in one file but in an alpha field in another file. Comparing fields of different types required me to convert one of the fields to the type of the other. Why couldn’t the computer do that conversion for me?

    In V5R3, IBM loosened up this restriction in certain places. I’ve discovered two of those places–SQL and Open Query File (OPNQRYF). It is possible as of V5R3 to compare numeric data to character data, with the computer handling the conversion. The rub is that the alpha field must contain valid numeric data.

    As a simple example, consider a file with two fields–a four-byte alpha field cleverly named ALPHA and a five-digit numeric field of zero decimal places named NUM. The following query uses the DECIMAL function to illustrate what the system will and will not like in character-to-numeric comparisons.

    select num, alpha, dec (alpha,4,0)
    from some file
    

    If I execute this query in interactive SQL, this is what I see.

    NUM     ALPHA      DEC 
      3-    -3           3-
      3-    - 3      ++++++
      3-    -  3     ++++++
      3-    3-       ++++++
      0              ++++++
      1     1            1 
      2      2           2 
      3     +3           3 
      4     0004         4 
      4     0  4     ++++++
      5     5.0          5 
     40     4 0      ++++++
    

    The lines with the plus signs in the third column show which character values will not convert to numeric during a character-to-numeric comparison. Notice what the system likes. Leading and trailing blanks are ignored, but embedded ones are not. You may include a leading plus sign or minus sign, and you may include a decimal point.

    So far I have used character-to-numeric comparisons in two SQL clauses–WHERE and JOIN–and they work as I expected.

    select *
       from SomeFile
       where CharField > NumField
    

    OPNQRYF also allows comparison of character to numeric data. The following commands select the records where an alpha field is equal to a numeric field.

    OPNQRYF FILE((ONEFILE))
       QRYSLT('alpha *eq num') 
    CPYFRMQRYF FROMOPNID(ONEFILE) TOFILE(ANOTHER) +
       CRTFILE(*YES)
    

    However, Query/400 does not allow character-to-numeric comparisons, at least not in V5R3.

    –Ted

    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

    Sponsored Links

    Bytware:  Network security, anti-virus, monitoring, notification/alerts, file recovery, & compliance
    COMMON:  Join us at the Fall 2006 conference, September 17-21, in Miami Beach, Florida
    ProData Computer Services:  Use Server Proven DBU-on-demand for $10 a day anytime, anywhere!

    TMW Systems Gets New CEO Sun Microsystems Begins Taking Java Open Source

    Leave a Reply Cancel reply

Volume 6, Number 20 -- May 17, 2006
THIS ISSUE SPONSORED BY:

WorksRight Software
Asymex
Profound Logic Software

Table of Contents

  • Debugging iSeries Java Applications Remotely
  • Common Sense Comparisons
  • STRCPYSCN: The Poor Man’s 5250 Remote Control Program

Content archive

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

Recent Posts

  • The Power11 Transistor Count Discrepancies Explained – Sort Of
  • Is Your IBM i HA/DR Actually Tested – Or Just Installed?
  • Big Blue Delivers IBM i Customer Requests In ACS Update
  • New DbToo SDK Hooks RPG And Db2 For i To External Services
  • IBM i PTF Guide, Volume 27, Number 33
  • Tool Aims To Streamline Git Integration For Old School IBM i Devs
  • IBM To Add Full System Replication And FlashCopy To PowerHA
  • Guru: Decoding Base64 ASCII
  • The Price Tweaking Continues For Power Systems
  • IBM i PTF Guide, Volume 27, Numbers 31 And 32

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