Home

Resources
 
 

Using Case To Segregate Data

Sometimes, you have list data that you want to appear in column format. The SQL Case statement can be used to create virtual columns that you can aggregate. Case in point: once of my clients was looking to put a daily sales report on their company intranet that grouped sales into categories based on the type of parts sold. The target report should look something like this:

Date Equipment Parts Supplies Total
01/01/20011,0005002501,750
01/02/20012,0002505002,750
01/03/20015005007001,750

To do this report, I needed to summarize the data in their invoice detail file joined to their item master file. Now, the data in the invoice detail and item master files have a lot of fields, but for this exercise I’m only interested in the following pieces of data:

 

FIELD TYPE
PARTIDINTEGER
CLASSCHAR(3)
EXTPRICEDECIMAL(12,2)
IDATEDATE


So, if you select these fields from the join, you might see something like the following.

 

PARTID CLASS EXTPRICE IDATE
123‘001’2.2201/01/2001
456‘500’4.4401/02/2001
789‘999’5.5601/01/2001
147‘503’9.5501/01/2001


In this clients data, the CLASS field is a 3 character field that contains the classification of the item. If the CLASS code is between ‘001’ and ‘499’ the item is considered to be a piece of equipment. If the class is between ‘500’ and ‘700’ the item is a part, and classes between ‘701’ and ‘999’ are considered supplies. By using the case statement, I can segregate the extended prices into virtual columns to make summing the data by category easier. Take a look at the following statement:
 

SELECT IDATE,
    	(CASE WHEN CLASS BETWEEN ‘001’ AND ‘499’ 
   		 THEN EXTPRICE ELSE 0) AS EQUIPMENT,
    	(CASE WHEN CLASS BETWEEN ‘500’ AND ‘700’ 
    		 THEN EXTPRICE ELSE 0) AS PARTS,
    	(CASE WHEN CLASS BETWEEN ‘701’ AND ‘999’ 
    		 THEN EXTPRICE ELSE 0) AS SUPPLIES,
    	EXTPRICE AS DAYTOT
    FROM IDETAILS

Executing the above statement on the data in the files would yield output like the following:

IDATE EQUIPMENT PARTS SUPPLIES DAYTOT
01/01/20012.22002.22
01/02/200104.4404.44
01/01/2001005.565.56
01/01/200109.5509.55

 

Note that based on the CLASS of the transaction, the value of the EXTPRICE field will be placed in the appropriate column. So, what I have done is create a statement that makes a virtual table where line items are placed into their appropriate slots.

Now, this is fine and dandy, but in my case I had about 55,000 line item details in my table and I am interested in seeing the totals by day, not 55,000 detail records segregated into columns. So, the next step is to create a common table expression out of the above select statement so that I can summaries all of the records into a virtual table of categorized sales by date. See, in the FROM clause of an SQL Select statement you can name a table or view that the data is to come from or you can use an SQL statement that yields a result table. I use these types of statements a lot in my consulting practice when I need to aggregate data and do not want to create an SQL view. Here is an example SQL statement that will summarize and categorize the sales by date:

SELECT IDATE, SUM(EQUIPMENT),
	    SUM(PARTS),SUM(SUPPLIES),SUM(DAYTOT)
    FROM ( SELECT IDATE, 
    	(CASE WHEN CLASS BETWEEN ‘001’ AND ‘499’ 
    		THEN EXTPRICE ELSE 0) AS EQUIPMENT,
    	(CASE WHEN CLASS BETWEEN ‘500’ AND ‘700’ 
    		THEN EXTPRICE ELSE 0) AS PARTS,
    	(CASE WHEN CLASS BETWEEN ‘701’ AND ‘999’ 
    		THEN EXTPRICE ELSE 0) AS SUPPLIES,
    	EXTPRICE AS DAYTOT
    FROM IDETAILS ) AS X
    GROUP BY IDATE
    ORDER BY IDATE

The statement works by first running the inner SQL statement named in the outer statement FROM clause to produce a result set named X. This result set is then fed into the outer statement and summarized to create the end set, which is an aggregation of sales by date. The important thing to note about the above statement is that the fields EQUIPMENT, PARTS, SUPPLIES and DAYTOT only exist for the duration of the SQL statement. They are truly virtual columns that are the results of the evaluation of the CASE expression.

The nice thing about letting SQL create the virtual columns and summarize the data for you is that it simplifies the coding of the web page on the web server. Basically, my code just has to execute the SQL statement, retrieve each record, put html tags around the fields and send it to the client browser. All of the work is being done on the AS/400, where it belongs, not on the web server.

Want to see the code on the web server? In the text area below is actual code that renders an intranet page.  Note that some functions are defined in my StdFuns include file.  These functions are for automatically formatting the data into user friendly format.  For example, the function bfn formats a number into a pretty string which is rendered in red if the number is negative.  Also, note that this is actual code from a client, not like the examples above.  This code reads a table called dailysales that we update each evening from the order details table.  The dailysales table uses the above case statements to break the componend data into categories like Equipment, Compressor, Supplies, Reclaim, etc.  The web page retrieves each category meta totals and uses their offset in the recordset to make rendering the page simple.

Working Code