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