Wednesday, May 20, 2015

Creating derived columns in SQL View

create view case column

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:

  1. Part description, I will get that from the file TESTFILE2.
  2. Extended amount (quantity * amount).
  3. Order status description.
  4. 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.

10 comments:

  1. Derived columns are great so thank you for the article.

    As 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.

    ReplyDelete
  2. 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.

    ReplyDelete
  3. Hi Simon, you always come to my help; I was looking for something like this. Thank you so much. Carlos. Super article!!!

    ReplyDelete
    Replies
    1. Thank you for letting me know that you find my work helpful.

      Delete
  4. giving error 'Column list required' when tried to create a view with same field names in multiple occurrence

    CREATE 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 '

    ReplyDelete
    Replies
    1. 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.

      Delete
    2. Thanks 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

      Delete
  5. You can use the same column names, you just have to list the columns you want in your view.

    For an example see here

    PS: It is Db2 for i now. It stopped being DB2/400 in 2000.

    ReplyDelete
  6. 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 ?

    ReplyDelete
    Replies
    1. Any 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.

      In 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.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.