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:
| Order Status | |
| Code | Description |
| P | Pending approval |
| A | Approve |
| D | Decline |
| S | Shipped |
Run Status is what is happening to the order at this time. The valid codes are:
| Run Status | |
| Code | Description |
| 00 | Not started |
| 10 | Started |
| 20 | Completed |
| 30 | Cancelled |
| 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.
Derived columns are great so thank you for the article.
ReplyDeleteAs of 7.1, you can have CREATE OR REPLACE VIEW.
If you have time, it would be nice to see an example where the Order Status Description and Run Status Description are retrieved from a single Description table and not hard-coded.
Nice example. The two status fields could be looked up in a single codes table too, which might make for an interesting and more complex example.
ReplyDeleteHi Simon, you always come to my help; I was looking for something like this. Thank you so much. Carlos. Super article!!!
ReplyDeleteThank you for letting me know that you find my work helpful.
Deletegiving error 'Column list required' when tried to create a view with same field names in multiple occurrence
ReplyDeleteCREATE VIEW CRMTEST AS
select t1.A_opptid44,
t1.A_OPPNAM44, t11.A_MISDDS10,
t1.A_OPPTYP44, t4.A_MISDDS10
from bcgcrmf1r0/crm44pf t1
join bcgcrmf1r0/crm10pf t4 on t1.A_OPPTYP44 = t4.A_MISDTL10
join bcgcrmf1r0/crm10pf t11 on t1.A_OPPNAM44 = t11.A_MISDTL10
group by t1.A_OPPTID44 '
Yes, as you have created a view that contains columns from more than one table you need to list which columns need to be included in the view.
DeleteThanks for the reply.....I want to use same column name multipal occurrence because my main file CRM44PF's join file is CRM10PF where I used to fetch descriptions for verious code names....this is DB2 400 database
DeleteYou can use the same column names, you just have to list the columns you want in your view.
ReplyDeleteFor an example see here
PS: It is Db2 for i now. It stopped being DB2/400 in 2000.
hi Simon Thank you for the great example of CASE. Please clarify me the following : you had defined here the Extended amount column as multiply ( calculating ) expression but did wrote this multiply in an SQL statement: Does it means that it should be done in a program getting input from this view ?
ReplyDeleteAny calculations or derived columns in the View are performed within the View. I am returned a column in the result set that is the Extended Amount. I do not have to perform any calculations.
DeleteIn my opinion this is the power of using Views. I can have all kinds of calculations, etc, within them that I do not have to perform in the program that uses it. Makes sure that all programs use the same logic, and means there is a lot less calculations I have to perform.
Try it. Build a test View for yourself with a calculation in it. Then view the View (you can use STRSQL or even RUNQRY to do so), and the calculated field will display.