Reader Feedback and Insights: Overlooking the Obvious
November 8, 2002 Timothy Prickett Morgan
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.
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
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( ).
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.
Using SET is particularly useful if you need to convert a character string to numeric. Use the DECIMAL or the INTEGER function.
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.
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.
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?
ADVANCED SYSTEMS CONCEPTS
SEQUEL FYI offers outstanding OLAP business intelligence functionality for a fraction of the cost of comparable solutions.