Problem solve Get help with specific problems with your technologies, process and projects.

Show alarms in SQL results

An image is worth more than a thousand words... or data. Learn how to show alarms in SQL results.

An image is worth more than a thousand words... or data

Often, when we view the SQL results, we miss a graphical alarm. In example: If we want to view the customers sales volume, it would be an interesting view in the different colors and the different volume ranges of sales.

To view different colors in the SQL results, we put the attrib byte before the field value (or line) to show. Like we 'paint' the comment lines in the SEU, we 'paint' the SQL results. Some codes that we can use:

    x'20' Normal
    x'21' Reverse
    x'22' HI
    x'23' HI reverse
    x'28' Red
    x'29' Red reverse
    x'2A' Blink
    x'2B' Blink reverse

To define the alarm ranges, we use the SQL CASE instruction. It run likely RPG IV CASE instruction.

|                                                                               |
|                                                                               |
|                                    +-ELSE NULL---------------+                |
| >--CASE----searched-when-clause----+-------------------------+--END---------> |
|          +-simple-when-clause---+  +-ELSE--result-expression-+                |
|                                                                               |
| searched-when-clause:                                                         |
|    <-----------------------------------------------------+                    |
| +----WHEN--search-condition--THEN----result-expression----------------------| |
|                                    +-NULL--------------+                      |
|                                                                               |
| simple-when-clause:                                                           |
|                <-----------------------------------------------+              |
| +--expression----WHEN--expression--THEN----result-expression----------------| |
|                                          +-NULL--------------+                |
|                                                                               |

In the next example, we show all tables on the system that have more of 100 fields, in HI that have more than 250 fields, in Red that have more than 1,000 and the library in blink and the rest in red, for those that have more than 2,500 fields.

To view the SQL result, run STRSQL command and put this instruction:

SELECT CASE                                                    
       WHEN count(*) > 2500 THEN (X'2A'||SYSTEM_TABLE_SCHEMA||X'28')  
       WHEN count(*) > 1000  THEN (X'28'||SYSTEM_TABLE_SCHEMA) 
       WHEN count(*) > 250 THEN (X'22'||SYSTEM_TABLE_SCHEMA)   
       else (' '||SYSTEM_TABLE_SCHEMA)                         
       end  AS LIB,                                            
       SYSTEM_TABLE_NAME AS TABLE,                             
       COUNT(*) AS FIELDS                                      
FROM QSYS2/SYSCOLUMNS                                          
group by system_table_schema, system_table_name                
having count(*) > 100    


The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

This was last published in October 2001

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.