| Editors: | Ted Holt | Managing Editor: | Mari Barrett | |
| Howard Arner | Technical Editor: | David Morris |
|
Volume 1, Number 20, sponsored by:Profound Logic Software, Inc.
|
|
|
|
|
|
Free Advice from Howard: Sometimes you have list data that you want users to view in column format. The SQL case statement can be used to create virtual columns that you can aggregate. Here is an example: One of my clients wanted to put on his intranet a daily sales report that grouped sales into categories based on the type of parts sold. The target report looked something like the following example:
To create this report to fit the client's need, I had to summarize the data in the company's invoice detail file that was joined to the company's item master file. The data in the invoice detail and item master files have many fields, but for this exercise, I'm only interested in the following pieces of data:
In the client's data, the CLASS field is a 3-character field that contains the item classification. If the CLASS code is between '001' and '499', the item is considered a piece of equipment. If the CLASS code is between '500' and '700', the item is a part, and CLASS codes between '701' and '999' indicate supplies. By using the SQL case statement, I can segregate the extended prices into virtual columns to make summing up the data by category easier. Look at the following SELECT 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 the following output:
I created a statement that makes a virtual table where line items are placed into their appropriate slots. Note that based on the CLASS of the transaction, the value of the EXTPRICE field will be placed in the appropriate column. This SELECT statement is fine, but in my case, I had about 55,000 line-item details in my table and I am interested in viewing the totals by day, not as 55,000 detail records segregated into columns. To solve that problem, I created a common table expression out of the earlier SELECT statement so that I can summarize all of the records into a virtual table of categorized sales by date. In the FROM clause of an SQL Select statement, you can name a table or view from which the data is to come or you can use an SQL SELECT statement that yields a result table. I use these types of statements frequently in my consulting practice when I need to aggregate data and do not want to create an SQL view. Here is an example of an 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. Note that the fields called 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. Allowing SQL to create the virtual columns and summarize the data for you 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 done where it belongs: on the AS/400 or iSeries, not on the Web server. -- Howard
Hey, Ted: I see you used the /copy command in the November 16, 2001, issue of Midrange Guru, OS/400 Edition. I do not like that thing. It makes debugging difficult. -- David Try compiling with DBGVIEW(*LIST). Do not specify OPTION(*NOSHOWCPY). Maybe you'll decide you like /copy as much as I do. -- Ted
|  |
|
Hey, Ted: Is there a way that I can force users to run Query in batch? -- William Yes, William. Change the Run Query (RUNQRY) command so that it is not allowed to run in interactive mode. First, make a copy of the RUNQRY command.
CRTDUPOBJ OBJ(RUNQRY) FROMLIB(QSYS) + OBJTYPE(*CMD) TOLIB(MYLIB) + NEWOBJ(RUNMYQRY) Next, change the RUNQRY command to the following:
With this change, users can no longer run queries in interactive mode. If you need to run queries interactively, use the copy you made of RUNQRY. When you update your machine to a new release of OS/400, the modified copy of the RUNQRY command will be replaced by a fresh copy from IBM. At that point, you'll have to modify the RUNQRY again so users cannot run queries in interactive mode. You might find it beneficial to save changes, such as the two commands in the preceding paragraphs, in a source member. After each upgrade, compile the source member and run it to change the defaults again. An alternate technique is to only prevent interactive queries that will run for more than a specified number of seconds. That way, users can run queries that return a page or two of data without having to search their submitted jobs for the query output. To do this, add the following line to your users startup program:
In this case, interactive queries that the query optimizer estimates will run in under 15 seconds are allowed. If the query optimizer estimates that the query will take longer, message CPA4259 is displayed. The message allows the query to continue if the user responds with an I. With trust and training, this can be a good alternative. -- Ted
Subscription and Advertising Information
Subscription Information To unsubscribe, change your email address, or sign up for any of Guild Companies, Inc's free email newsletters, visit http://www.itjungle.com. Hit the SUBSCRIBE button on the homepage and it will lead you to our online subscription system. When you sign up for one of our e-newsletters, you can be assured that your e-mail address will NEVER be sold to an outside company.
Advertising Information Please see our advertising opportunities and pricing at http://www.itjungle.com/advertising.html
Or contact Timothy Prickett Morgan at
Phone: 212 942 5818 Email: tpm@itjungle.com
If you have a tough problem, our gurus can probably help. Their mailboxes are always open. * Email Ted Holt at tholt@itjungle.com * Email Howard Arner at harner@itjungle.com | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
This document may be redistributed freely and enthusiastically by email, but only in its unedited form. Thanks for your cooperation.
Midrange Guru is a registered trademark of Guild Companies, Inc. IBM, AS/400, iSeries, OS/400, and eServer registered trademarks of International Business Machines Corp. All other product names are trademarked or copyrighted by their respective holders.
