• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Reader Feedback and Insights: Overlooking the Obvious

    November 8, 2002 Timothy Prickett Morgan

    Hey, Ted:

    I have been following the SQL tips in Midrange Guru (“SQL to the Rescue!” and “SQL’s One-Row, One-Column Table“) that use a one-row, one-column table to execute SQL functions.

    There’s an easier way.

    Use the SET statement instead.

    The following code is equivalent to the first tip on this subject, which was published in the October 25 issue.

    d found           s              1
    d field1          s              5u 0 inz(5)
    d field2          s              5u 0 inz(15)
    d field3          s              5u 0 inz(30)
    
    C/EXEC SQL
    C+ set :found =
    C+  case when max(:field1, :field2, :field3) > 25
    C+   then '1'
    C+   else '0'
    C+  end
    C/END-EXEC
    C     found         dsply
    

    Thanks for all the great stuff you publish. I wish Midrange Guru came out more than twice a week.

    — Russ

    In the October 30 Midrange Guru article, “SQL’s One-Row, One-Column Table,” you presented three examples that used a ‘dummy’ select statement to enable the use of  scalar functions. Nevertheless there is a more straightforward way to do just that:

    c/exec sql                                        
     +    set  :hexvalue =  hex(:charvalue)           
    c/end-exec                                        
    
    c/exec sql                                      
     +    set  :weekday = dayofweek(:duedate)       
    c/end-exec                                      
    
    c/exec sql                                                             
     +    set  :editednbr =  trim(leading '0' from :nbr)                   
    c/end-exec                                                             
    

    — Jan

    Use a SET statement instead of SELECT when the only purpose is to use an SQL scalar function, as in this example:

    C/exec sql                            
    C+    set :char = trim(:char)      
    C/end-exec                            
    
    C/exec sql                            
    C+    set :i = round(:x, 2)      
    C/end-exec                            
    

    Be careful. In some cases, there is no possible way to use a host variable as an argument for the function. For instance, I’ve had problems when I tried to use a host variable for the second parameter in round( ).

    — Carlos

    This is in reference to your October 30, 2002, article. Instead of using the QSQPTABL table, why not use the SET variable statement? Here is an example I pulled out of one of my working programs:

    DayofYear       s              7  0 inz(*zeros)    
    TranDate        s                   like(ctDaRun)
                                                     
    c/Exec Sql                                     
    c+   set :DayofYear =  dayofyear(:tranDate)    
    c/End-Exec                                     
    

    The SET statement can only be used in an embedded application and cannot be dynamically prepared.

     

    — Kent

    Using SET is particularly useful if you need to convert a character string to numeric. Use the DECIMAL or the INTEGER function.

    — Chris

    I’ve been looking at these examples of SQL where a SELECT/INTO is used, along with this QSQPTABL table, used as a dummy. This seems kind of silly when the SET statement is available.

    — Doug

    My guess is that using SET produces the same executable code that the one-row table technique produces, but the meaning is clearer. I know this works because I have successfully used the HEX function in a COBOL UDF to parse out a field from a “legacy” disk file with one long character field containing many packed decimal values.

    — Stan

    Thanks also to Dave, Sam, and Bill who also wrote to me with alternative solutions. Now, does anybody have a use for a one-row, one-column table?

    — Ted

    Sponsored By
    ADVANCED SYSTEMS CONCEPTS

    Business Analytics
    – Practical –
    – Cost Effective –
    – Easy to Deploy –

    SEQUEL FYI

    User Quote:
    “I love SEQUEL FYI because it lets me look at the data any way I need to see it, instantly. This is the easiest tool to manage complex product relationships that I have ever seen.”

    SEQUEL FYI offers outstanding OLAP business intelligence functionality for a fraction of the cost of comparable solutions.

    Read More > View Streaming Video

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 86 -- November 8, 2002

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Use the Full-Screen Debugger with OPM Programs Reader Feedback and Insights: Modernizing RPG II Programs

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 86

This Issue Sponsored By

    Table of Contents

    • Reader Feedback and Insights: Overlooking the Obvious
    • Odds and Ends
    • Omitting Parameters in RPG Programs and Modules

    Content archive

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

    Recent Posts

    • POWERUp 2025 –Your Source For IBM i 7.6 Information
    • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
    • Guru: Creating An SQL Stored Procedure That Returns A Result Set
    • As I See It: At Any Cost
    • IBM i PTF Guide, Volume 27, Number 19
    • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
    • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
    • i-Rays Brings Observability To IBM i Performance Problems
    • Another Non-TR “Technology Refresh” Happens With IBM i TR6
    • IBM i PTF Guide, Volume 27, Number 18

    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