A SQL View is a virtual table. Its columns can come from one or more SQL tables or DDS files. The data in the View is not stored in its own database object. When the View is used the Select statement is executed to produce the data. You can also create, what I call, "derived" columns. The "derived" columns do not exist in the original tables/files, they are calculated when the Select statement is run.
In the examples I am going to show how I can code "derived" fields in a View to:
- Calculate a new numeric column.
- Create a description column for values in an existing column.
I know I am going to get critical comments about this post as I am going to build a View over DDS files, rather than SQL tables. I am using DDS files as most of us still work in environments where DDS files predominate. Learning how to use Views will help you to learn and transition to a SQL table world. Also, the file in these examples is very simple as it is just for illustrating how to do this.
So let's start with an Order file, called TESTFILE.
A UNIQUE A R TESTFILER A ORDNBR 10A TEXT('Order number') A COLHDG('Order' 'No.') A PARTNBR 15A TEXT('Part number') A COLHDG('Part' 'No.') A ORDQTY 7P 0 TEXT('Order quantity') A COLHDG('Order' 'qty') A ORDAMT 9P 2 TEXT('Order amount') A COLHDG('Order' 'amt') A ORDSTS 1A TEXT('Order status') A COLHDG('Ord' 'sts') A RUNSTS 2A TEXT('Run status') A COLHDG('Run' 'sts') A K ORDNBR
TESTFILE contains 7 records:
Order Part Order Order Ord Run No. No. qty amt sts sts 1 ABC123 100 1.07 P 00 2 DEF456 1 1.07 A 10 3 XYZ999 1 .54 D 20 4 123ABC 12 6.50 A 30 5 456ASD 1 4.75 S 99 7 NO PART# 0 .00 X 88
The seventh record has an invalid Part number.
The valid Order statuses are:
Run Status is what is happening to the order at this time. The valid codes are:
|99||Completed and posted to financial application|
In any database I would expect to find both of these in files. But for this example they are not, and I am the only person who knows what these codes mean. I want to create a View that contains all the fields/columns from TESTFILE as well as the following additional columns:
- Part description, I will get that from the file TESTFILE2.
- Extended amount (quantity * amount).
- Order status description.
- Run status description.
I am going to place the DDL for this View in a source member and then create it using the 'Run SQL Statements' command, RUNSQLSTM. Below is the example source I made for this view, TESTFILEV:
01 DROP VIEW MYLIB/TESTFILEV ; 02 CREATE VIEW MYLIB/TESTFILEV ( 03 ORDER_NBR FOR "ORDNBR", 04 PART_NBR FOR "PARTNBR", 05 PART_DESC FOR "PARTDESC", 06 ORDER_QTY FOR "ORDQTY", 07 ORDER_AMT FOR "ORDAMT", 08 ORDER_EXTENDED_AMT, 09 ORDER_STS FOR "ORDSTS", 10 ORDER_STS_DESC, 11 RUN_STS FOR "RUNSTS", 12 RUN_STS_DESC) 13 AS SELECT A.ORDNBR,A.PARTNBR,B.PARTDESC,A.ORDQTY,A.ORDAMT, 14 A.ORDQTY * A.ORDAMT,A.ORDSTS, 15 CASE WHEN ORDSTS = 'P' THEN 'PENDING' 16 WHEN ORDSTS = 'A' THEN 'APPROVED' 17 WHEN ORDSTS = 'D' THEN 'DECLINED' 18 WHEN ORDSTS = 'S' THEN 'SHIPPED' 19 ELSE 'UNKNOWN' 20 END, 21 A.RUNSTS, 22 CASE WHEN RUNSTS = '00' THEN 'NOT STARTED' 23 WHEN RUNSTS = '10' THEN 'STARTED' 24 WHEN RUNSTS = '20' THEN 'COMPLETED' 25 WHEN RUNSTS = '30' THEN 'CANCELLED' 26 WHEN RUNSTS = '99' THEN 'POSTED' 27 ELSE RUNSTS 28 END 29 FROM MYLIB/TESTFILE A LEFT OUTER JOIN MYLIB/TESTFILE2 B 30 ON A.PARTNBR = B.PARTNBR ; 31 LABEL ON TABLE MYLIB/TESTFILEV IS 'SQL view of TESTFILE' ; 32 LABEL ON COLUMN MYLIB/TESTFILEV ( 33 ORDER_EXTENDED_AMT IS 'Order Ext Amt', 34 ORDER_STS_DESC IS 'Order Status Description', 35 RUN_STS_DESC IS 'Run Status Description' 36 ) ; 37 LABEL ON COLUMN MYLIB/TESTFILEV ( 38 ORDER_EXTENDED_AMT TEXT IS 'Order Extended Amount', 39 ORDER_STS_DESC TEXT IS 'Order Status Description', 40 RUN_STS_DESC TEXT IS 'Run Status Description' 41 ) ;
On line 1 delete the View, if it already exists, using DROP VIEW.
Line 2 is where the code starts to create my view. Lines 3-12 is where I give the names of the columns I want in the view. Some are in TESTFILE or TESTFILE2, others, such as ORDER_EXTENDED_AMT, are not. By using FOR the column in the View adopts the attributes of the field in the based on file.
Lines 13-30 are the Select statement. The fields/columns to be included are listed. The sixth column, on line 14, is where the calculation of the extended amount happens. This will be placed in the sixth column of the view, which happens to be ORDER_EXTENDED_AMT.
Lines 15-20 is where I define the description for the 'Order Status', ORDSTS. The CASE is like a Select group in RPG. It starts on line 15 with word CASE, and ends on line 20 with the word END. The WHEN is where the check is performed, and if the statement is true the action that follows the THEN is performed. The ELSE, line 19, is only performed if none of the WHEN statements were true. In this case the column will contain 'UNKNOWN'.
The second Case statement is between lines 22-28, this time for the 'Run Status' description. It is the same as the previous Case, except for the ELSE, line 28. Rather than use a string in the column as I did for the 'Order Status description' I am going to use the value that is in the 'Run Status' field.
On line 29 I am defining the join between the two files as 'left outer', which means that all records/rows in the first file/table will be included even if a match cannot be found in the second. Line 30 is where I define what the "key" is to join the two tables/files.
I use the LABEL ON TABLE, line 31, for the object text for the View.
As ORDER_EXTENDED_AMT, ORDER_STS_DESC, and RUN_STS_DESC are new columns I could not use the FOR as I did for the other columns. This means that they do not have what SQL calls labels on them. Lines 32-36 is where I label the columns, equivalent to COLHDG in a DDS file. On lines 37-41 I add the text for the columns, equivalent of the TEXT in a DDS file.
If I look at the view using a SQL statement or even the RUNQRY command it would look like:
Order Part Part Order Order Order Ord Order Status Run Run Status No. No. description qty amt Ext Amt sts Description sts Description 1 ABC123 BLACK INK PEN 100 1.07 107.00 P PENDING 00 NOT STARTED 2 DEF456 RED INK PEN 1 1.07 1.07 A APPROVED 10 STARTED 3 XYZ999 PENCIL 1 .54 .54 D DECLINED 20 COMPLETED 4 123ABC STAPLER 12 6.50 78.00 A APPROVED 30 CANCELLED 5 456ASD STAPLE REMOVER 1 4.75 4.75 S SHIPPED 99 POSTED 7 NO PART# - 0 .00 .00 X UNKNOWN 88 88
The row for order 7, with the invalid part number, shows a dash ( - ) for the 'Part description', where it is really null.
This is just a simple example of how to use the "derived" columns in a SQL view. I am sure you can come up with better uses of it in your working environment.
You can learn more about this on the IBM website:
This article was written for IBM i 7.2, and should work for earlier releases too.