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

    Begin Your Journey to the Cloud with Hybrid Cloud Date Protection and Disaster Recovery

    FalconStor StorSafe optimizes and modernizes your IBM i on-premises and in the IBM Power Virtual Server Cloud

    FalconStor powers secure and encrypted IBM i backups on-premise and now, working with IBM, powers migration to the IBM PowerVS cloud and on-going backup to IBM cloud object storage.

    Now you can use the IBM PowerVS Cloud as your secure offsite copy and take advantage of a hybrid cloud architecture or you can migrate workloads – test & development or even production apps – to the Power VS Cloud with secure cloud-native backup, powered by FalconStor and proven IBM partners.

    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

    • To Comfort The Afflicted And Afflict The Comfortable
    • How FalconStor Is Reinventing Itself, And Why IBM Noticed
    • Guru: When Procedure Driven RPG Really Works
    • Vendors Fill In The Gaps With IBM’s New MFA Solution
    • IBM i PTF Guide, Volume 27, Number 27
    • With Power11, Power Systems “Go To Eleven”
    • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
    • Izzi Buys CNX, Eyes Valence Port To System Z
    • IBM i Shops “Attacking” Security Concerns, Study Shows
    • IBM i PTF Guide, Volume 27, Number 26

    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