Reader Feedback and Insights: SQL to the Rescue!

October 25, 2002 Timothy Prickett Morgan

Hey, Ted:

I am writing in regard to the September 20, 2002 Midrange Guru article, “Comparing Lists of Numbers to a Value .” I would like to offer the following embedded SQL example as an alternative solution:

D found           s              1a              
D
 * if (field1 or field2 or field3) > 25 
C/exec sql                                       
C+    select case                                
C+      when max(:field1, :field2, :field3) > 25 
C+         then '1'                              
C+         else '0'                              
C+      end                                      
C+    into :found                                
C+    from (any file with one record)
C/end-exec                                       
C                                                
C                   if        found = *on        
 .................. whatever ..................
C                   else 
................... whatever ..................
C                   endif

This program uses the SQL CASE statement to evaluate whether any one of a list of input host variable values is greater than a control value. A pseudo-Boolean character value is returned, indicating whether the condition was satisfied. The appropriate program logic can then be executed based upon the resulting value.

— Jeff

Thanks for the idea, Jeff. Isn’t it silly that you have to have a one-record dummy file for these things?

— 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