Using Union to combine data from two files into one View

When, in the ERP application my employer uses, an order is closed and posted to General Ledger the order's data is copied from the live file to the history file, and then deleted from the live file. This becomes an inconvenience when I am asked to produce a list of orders for a customer over a date range; the eligible data could be in both files. I have always wanted a way to link the live and history files together so that I can get the data from one place in the order I want.

Like most IBM i developers I want to use SQL to get data rather than use RPG's data access operation codes. I want to link these files together using SQL as I can get large chunks of data in one SQL operation faster than I can in RPG. Fortunately there is something in SQL to give me what I need, the UNION clause, that will allow me to join two or more selects statements together.

But before I describe it I think it will be useful to give some information on the example files I will be using. I have two Order Header files, ORDHDR and ORDHDRH, two Order detail files, ORDDTL and ORDDTLH, and a Vendor file, VENDOR. Both of the Order Header and both of the Order Details files are identical, except for the record format names.

* Order Header 01 A UNIQUE 02 A R RORDHDR 03 A ORDNBR 7A 04 A ORDDTE 8P 0 05 A VENNBR 10A 06 A DUEDTE R REFFLD(ORDDTE *SRC) 07 A ORDSTS 2S 0 08 A K ORDNBR 09 A K ORDDTE

* Order Header History 01 A REF(ORDHDR) 02 A UNIQUE 03 A R RORDHDRH 04 A ORDNBR R 05 A ORDDTE R 06 A VENNBR R 07 A DUEDTE R 08 A ORDSTS R 09 A K ORDNBR 10 A K ORDDTE

As these are only examples both of the files contain two records:

Order Header ORDNBR ORDDTE VENNBR ORDDTE ORDSTS 200711 20,170,125 V1054 20,171,005 20 200855 20,170,126 V1054 20,170,901 30

Order Header History ORDNBR ORDDTE VENNBR ORDDTE ORDSTS 110021 20,150,918 V1054 20,151,231 99 198860 20,151,203 V1054 20,160,202 99

I can join both of the files together using what I know as a multiformat logical file, see below. As you can see I just give the record format names of the two physical files and the key order I want.

01 A R RORDHDR PFILE(ORDHDR) 02 A K ORDDTE 03 A K ORDNBR * 04 A R RORDHDRH PFILE(ORDHDRH) 05 A K ORDDTE 06 A K ORDNBR

I can read the multiformat logical file in RPG, see below, and get the records in my sort order from both record formats if I read using the file name, rather than the record format names.

01 dcl-f ORDHDRL0 keyed ; 02 dow (1 = 1) ; 03 read ORDHDRL0 ; 04 if (%eof) ; 05 leave ; 06 endif ; 07 dsply ('Order No. = <' + ORDNBR + '>') ; 08 enddo ;

DSPLY Order No. = <110021 > DSPLY Order No. = <198860 > DSPLY Order No. = <200711 > DSPLY Order No. = <200855 >

If I try to use the multiformat with SQL I get the following message:

SELECT * FROM ORDHDRL0 File ORDHDRL0 in *LIBL has more than one format.

What I want to do is create a SQL View of rows/records from the live and history files. I can select the columns/rows I want in a Select statement, but I cannot join the data from the two files in the manner I want to do. This is where the UNION clause comes to the rescue. When I use the UNION I can combine two Select statements together, for example this statement below will join the rows/records from the live and history files into one set of results.

SELECT * FROM ORDHDR UNION SELECT * FROM ORDHDRH

My View will be a bit more complex as I want it to contain the matching information from the Vendor file, give the columns/fields long names, and good column headings and field texts.

01 CREATE OR REPLACE VIEW MYLIB.ORDHDRV0 ( 02 ORDER_NBR FOR "ORDNBR", 03 ORDER_ENTRY_DATE FOR "ORDDTE", 04 VENDOR_NBR FOR "VENNBR", 05 ORDER_DUE_DATE FOR "DUEDTE", 06 ORDER_STATUS FOR "ORDSTS", 07 VENDOR_NAME FOR "VENNME", 08 VENDOR_ADDRESS_1 FOR "VENADR1", 09 VENDOR_CITY FOR "VENCITY", 10 VENDOR_STATE FOR "VENSTATE", 11 VENDOR_ZIP FOR "VENPCDE", 12 VENDOR_COUNTRY FOR "VENCNTRY" 13 ) 14 AS 15 SELECT A.ORDNBR, A.ORDDTE, A.VENNBR, A.DUEDTE, A.ORDSTS, 16 B.VENNAME, B.VENADR1, B.VENCITY, B.VENSTATE, 17 B.VENPCDE, B.VENCNTRY 18 FROM ORDHDR A LEFT OUTER JOIN VENDOR B 19 ON A.VENNBR = B.VENNBR 20 UNION 21 SELECT A.ORDNBR, A.ORDDTE, A.VENNBR, A.DUEDTE, A.ORDSTS, 22 B.VENNAME, B.VENADR1, B.VENCITY, B.VENSTATE, 23 B.VENPCDE, B.VENCNTRY 24 FROM ORDHDRH A LEFT OUTER JOIN VENDOR B 25 ON A.VENNBR = B.VENNBR 26 RCDFMT RORDHDRV0 ; 27 LABEL ON COLUMN ORDHDRV0 ( 28 ORDER_NBR IS 'Order No.', 29 ORDER_ENTRY_DATE IS 'Order Entry date', 30 VENDOR_NBR IS 'Vendor No.', 31 ORDER_DUE_DATE IS 'Order Due date', 32 ORDER_STATUS IS 'Order status', 33 VENDOR_NAME IS 'Vendor name', 34 VENDOR_ADDRESS_1 IS 'Vendor address 1', 35 VENDOR_CITY IS 'Vendor city', 36 VENDOR_STATE IS 'Vendor state', 37 VENDOR_ZIP IS 'Vendor zip code', 38 VENDOR_COUNTRY IS 'Vendor country' 39 ) ; 40 LABEL ON COLUMN ORDHDRV0 ( 41 ORDER_NBR TEXT IS 'Order number', 42 ORDER_ENTRY_DATE TEXT IS 'Order entry date', 43 VENDOR_NBR TEXT IS 'Vendor number', 44 ORDER_DUE_DATE TEXT IS 'Order due date', 45 ORDER_STATUS TEXT IS 'Order status code', 46 VENDOR_NAME TEXT IS 'Vendor name', 47 VENDOR_ADDRESS_1 TEXT IS 'Vendor address address line 1', 48 VENDOR_CITY TEXT IS 'Vendor address city', 49 VENDOR_STATE TEXT IS 'Vendor address state', 50 VENDOR_ZIP TEXT IS 'Vendor address zip/postal code', 51 VENDOR_COUNTRY TEXT IS 'Vendor address country code' 52 ) ;

Line 1: As the IBM i I am using is running 7.3 I can use the CREATE OR REPLACE statement. If you are running an old version of the operating system you will need to just use CREATE.

Lines 2 – 12: As I have joined two files together I need to give the columns/fields I want to be in this View. I am giving each column a long name and I am using the name of the file's fields for the short name.

Lines 15 – 19: A Union contains two or more subselects. This Select is the first one and joins the live Order Header file to the Vendor file.

Line 20: This is the all-important UNION clause that joins the result set from the first subselect to the second subselect's results.

Line 21 – 25: This subselect is identical to the first subselect, except this one uses the Order Header history file.

Line 26: I don't have to give this a record format name, but I do in case I ever need to read this View using RPG. I cannot think why I would, but you never know.

Lines 27 – 39: This where I am giving the columns/fields the equivalent of column headings.

Line 40 – 52: Here I am giving the columns/fields the equivalent of field text.

Having created by view using the Run SQL Statement command, RUNSQLSTM, I can check to see if the files are joined and union-ed the way I want:

SELECT * FROM ORDHDRV0 ORDER BY ORDER_NBR,ORDER_ENTRY_DATE Order Order Entry Vendor Order Due Order Vendor Vendor No. date No. date status name address 1 110021 20,150,918 V1054 20,151,231 99 OFFICE SUPPLY INC. 116 FIRST ST 198860 20,151,203 V1054 20,160,202 99 OFFICE SUPPLY INC. 116 FIRST ST 200711 20,170,125 V1054 20,171,005 20 OFFICE SUPPLY INC. 116 FIRST ST 200855 20,170,126 V1054 20,170,901 30 OFFICE SUPPLY INC. 116 FIRST ST

It should come as no surprise that is there is an Order Header and Order Header History files there are also an Order Details and Order Detail History file too.

* Order Detail 01 A REF(ORDHDR) 02 A UNIQUE 03 A R RORDDTL 04 A ORDNBR R 05 A ORDDTE R 06 A LINNBR 5P 0 07 A PART 15A 08 A LINQTY 7P 0 09 A UPRICE 7P 3 10 A LINSTS R REFFLD(ORDSTS) 11 A K ORDNBR 12 A K ORDDTE 13 A K LINNBR

* Order Detail History 01 A REF(ORDDTL) 02 A UNIQUE 03 A R RORDDTLH 04 A ORDNBR R 05 A ORDDTE R 06 A LINNBR R 07 A PART R 08 A LINQTY R 09 A UPRICE R 10 A LINSTS R 11 A K ORDNBR 12 A K ORDDTE 13 A K LINNBR

As these are only examples both of the files contain a few records:

Order Detail ORDNBR ORDDTE LINNBR PART LINQTY UPRICE ORDSTS 200711 20,170,125 1 YW0717 20 .500 20 200711 20,170,125 2 PE11834 1 1.250 20 200711 20,170,125 3 PP1508-B 2 6.000 20 200855 20,170,126 1 PE34415 10 .300 40 200855 20,170,126 2 HE21007 45 1.500 20

Order Detail History ORDNBR ORDDTE LINNBR PART LINQTY UPRICE ORDSTS 110021 20,150,918 1 LX3144 400 .050 99 198860 20,151,203 1 KG17240 13 25.000 99 198860 20,151,203 2 ES11407 24 11.400 99

The code needed to union these two files together is just like the previous View's definition.

01 CREATE OR REPLACE VIEW MYLIB.ORDDTLV0 ( 02 ORDER_NBR FOR "ORDNBR", 03 ORDER_ENTRY_DATE FOR "ORDDTE", 04 LINE_NBR FOR "LINENBR", 05 PART_NBR FOR "PART", 06 LINE_QTY FOR "LINEQTY", 07 UNIT_PRICE FOR "UNITPRICE", 08 EXTENDED_PRICE FOR "EXTPRICE", 09 LINE_STATUS FOR "LINSTS" 10 ) 11 AS 12 SELECT ORDNBR,ORDDTE,LINNBR,PART,LINQTY, 13 UPRICE,(LINQTY * UPRICE),LINSTS 14 FROM ORDDTL 15 UNION 16 SELECT ORDNBR,ORDDTE,LINNBR,PART,LINQTY, 17 UPRICE,(LINQTY * UPRICE),LINSTS 18 FROM ORDDTLH 19 RCDFMT RORDDTLV0 ; 20 LABEL ON COLUMN ORDDTLV0 ( 21 ORDER_NBR IS 'Order No.', 22 ORDER_ENTRY_DATE IS 'Order Entry date', 23 LINE_NBR IS 'Line No.', 24 PART_NBR IS 'Part number', 25 LINE_QTY IS 'Line qty', 26 UNIT_PRICE IS 'Unit price', 27 EXTENDED_PRICE IS 'Extended price', 28 LINE_STATUS IS 'Line status' 29 ) ; 30 LABEL ON COLUMN ORDDTLV0 ( 31 ORDER_NBR TEXT IS 'Order number', 32 ORDER_ENTRY_DATE TEXT IS 'Order entry date', 33 LINE_NBR TEXT IS 'Order line number', 34 PART_NBR TEXT IS 'Part number', 35 LINE_QTY TEXT IS 'Order line quantity', 36 UNIT_PRICE TEXT IS 'Unit price', 37 EXTENDED_PRICE TEXT IS 'Extended price', 38 LINE_STATUS TEXT IS 'Order line status' 39 ) ;

Notice that on lines 14 and 17 I have calculated the extended price. By doing this in the View every time I use the View the extended price is recalculated, and I don't have to do the calculation in whatever program is using this View.

Once created the new View combines the data from the two Details files:

SELECT * ORDDTLV0 ORDER BY ORDER_NBR,ORDER_ENTRY_DATE,LINE_NBR Order Order Entry Line Part Line Unit Extended Line No. date No. number qty price price status 110021 20,150,918 1 LX3144 400 .050 20.000 99 198860 20,151,203 1 KG17240 13 25.000 325.000 99 198860 20,151,203 2 ES11407 24 11.400 273.600 99 200711 20,170,125 1 YW0717 20 .500 10.000 20 200711 20,170,125 2 PE11834 1 1.250 1.250 20 200711 20,170,125 3 PP1508-B 2 6.000 12.000 20 200855 20,170,126 1 PE34415 10 .300 3.000 40 200855 20,170,126 2 HE21007 45 1.500 67.500 20

As I have two views for the Header and Detail data I want to create third View that will combine the all of the data into one.

"Won't this be a performance hit?" I can hear some of you ask. "Surely it is not efficient to build a View over other views. You should build a View that joins all of these files together."

One of the wonderful parts of the IBM i operating system is the DB2 for i database engine. Whenever a SQL statement is run the "engine" works out the optimal way to get the data. When I build Views over other Views the "engine" does not build each View in turn, it works out the most efficient way to get the data from the available files and the access paths they have over them and gets it in one go. That is very cool!

Therefore, my third View is a combination of the two earlier ones.

01 CREATE OR REPLACE VIEW MYLIB.ORDALLV0 ( 02 ORDER_NBR FOR "ORDNBR", 03 ORDER_ENTRY_DATE FOR "ORDDTE", 04 LINE_NBR FOR "LINENBR", 05 ORDER_STATUS FOR "ORDSTS", 06 LINE_STATUS FOR "LINSTS", 07 ORDER_DUE_DATE FOR "DUEDTE", 08 VENDOR_NBR FOR "VENNBR", 09 PART_NBR FOR "PART", 10 LINE_QTY FOR "LINEQTY", 11 UNIT_PRICE FOR "UNITPRICE", 12 EXTENDED_PRICE FOR "EXTPRICE", 13 VENDOR_NAME FOR "VENNME", 14 VENDOR_ADDRESS_1 FOR "VENADR1", 15 VENDOR_CITY FOR "VENCITY", 16 VENDOR_STATE FOR "VENSTATE", 17 VENDOR_ZIP FOR "VENPCDE", 18 VENDOR_COUNTRY FOR "VENCNTRY" 19 ) 20 AS 21 SELECT A.ORDER_NBR,A.ORDER_ENTRY_DATE,B.LINE_NBR, 22 A.ORDER_STATUS,B.LINE_STATUS, 23 A.ORDER_DUE_DATE,A.VENDOR_NBR, 24 B.PART_NBR,B.LINE_QTY,B.UNIT_PRICE, 25 B.EXTENDED_PRICE, 26 A.VENDOR_NAME,A.VENDOR_ADDRESS_1, 27 A.VENDOR_CITY,A.VENDOR_STATE,A.VENDOR_ZIP, 28 A.VENDOR_COUNTRY 29 FROM ORDHDRV0 A LEFT OUTER JOIN ORDDTLV0 B 30 ON A.ORDER_NBR = B.ORDER_NBR 31 AND A.ORDER_ENTRY_DATE = B.ORDER_ENTRY_DATE 32 RCDFMT RORDALLV0 ;

After creating this view I can use it to see the information about any part of the order:

SELECT * FROM ORDALLV0 ORDER BY ORDER_NBR,ORDER_ENTRY_DATE,LINE_NBR Order Order Entry Line Order Line Order Due Vendor Part Line No. date No. status status date No. number qty 110021 20,150,918 1 99 99 20,151,231 V1054 LX3144 400 198860 20,151,203 1 99 99 20,160,202 V1054 KG17240 13 198860 20,151,203 2 99 99 20,160,202 V1054 ES11407 24 200711 20,170,125 1 20 20 20,171,005 V1054 YW0717 20 200711 20,170,125 2 20 20 20,171,005 V1054 PE11834 1 200711 20,170,125 3 20 20 20,171,005 V1054 PP1508-B 2 200855 20,170,126 1 30 40 20,170,901 V1054 PE34415 10 200855 20,170,126 2 30 20 20,170,901 V1054 HE21007 45

I am finding I am using Views more and more as I can create one just the way I want and use it in as many places as I want.

 

You can learn more about the SQL's UNION clause from the IBM website here.

 

This article was written for IBM i 7.3, and should work for earlier releases too.