Multiformat SQL Data Sets
April 30, 2008 Hey, Ted
|
DDS-defined logical files can have multiple record formats, each one of them coming from different physical files of different types of data. I would like to do the same sort of thing in SQL. That is, I want to retrieve all the records from one file followed by all the records from a second file, grouped by one or more common key fields. This is not a join, and it doesn’t seem like a union either, because the two data sets are so different. Am I trying to do the impossible? –David What you’re doing may be unusual, but it’s not unrealistic. You’re right that you don’t need a join. Despite the difference in the two types of data, you need a union. Since both tables have columns that have no counterpart in the other one, you’ll need to include nulls or default values as placeholders. I can explain this with a simple example. Let’s say that we have a table of items that customers have ordered from us.
create table orders
(Order dec(5),
Item char(4),
Qty dec(3))
insert into orders values
(201, 'A946', 5),
(203, 'B212', 7),
(207, 'A104', 12),
(210, 'B212', 4),
(211, 'B212', 4)
We have another table that shows where items are stored in the warehouse.
create table inventory
(Item char(4),
Location char(5),
Qty dec(3))
insert into inventory values
('A104', '1103B', 20),
('A104', '1412B', 6),
('A726', '0902A', 4),
('B212', '0312C', 8),
('B212', '0404B', 8),
('B212', '0411C', 6)
Here’s how we might combine the data into one set.
select ord.item, 'A' as ID,
ord.order, ord.qty as OrdQty,
cast(null as char(5)) as Loc,
cast(null as dec(3)) as LocQty
from orders as ord
union all
select inv.item, 'B', cast(null as dec(5)),
cast(null as dec(3)),
inv.location, inv.qty
from inventory as inv
order by 1,2,3,5
Now take a look at the result, and then I’ll explain the query in a bit more detail. ITEM ID ORDER ORDQTY LOC LOCQTY ==== == ===== ====== ===== ====== A104 A 207 12 - - A104 B - - 1103B 20 A104 B - - 1412B 6 A726 B - - 0902A 4 A946 A 201 5 - - B212 A 203 7 - - B212 A 210 4 - - B212 A 211 4 - - B212 B - - 0312C 8 B212 B - - 0404B 8 B212 B - - 0411C 6 The first SELECT (shown below) retrieves order information. Notice that the location and location quantity columns are left null, since they don’t exist in the orders. Also notice that I have forced an “A” into the second column, in order to sort the data and also to identify the row as an order row.
select ord.item, 'A' as ID,
ord.order, ord.qty as OrdQty,
cast(null as char(5)) as Loc,
cast(null as dec(3)) as LocQty
from orders as ord
The second SELECT retrieves inventory information. Notice that the order fields are loaded with nulls.
select inv.item, 'B', cast(null as dec(5)),
cast(null as dec(3)),
inv.location, inv.qty
from inventory as inv
The second column identifies each type of record and aids in sorting the data properly. I forced an “A” into order records and a “B” into inventory records. If I were to embed this statement in an RPG program, for example, my program could use this field to identify the type of data in a row. If you don’t want to deal with nulls, you can use blanks and zeros instead, as the following query illustrates.
select ord.item, 'A' as ID,
ord.order, ord.qty as OrdQty,
' ' as Loc,
0 as LocQty
from orders as ord
union all
select inv.item, 'B', 0, 0,
inv.location, inv.qty
from inventory as inv
order by 1,2,3,5
ITEM ID ORDER ORDQTY LOC LOCQTY
==== == ===== ====== ===== ======
A104 A 207 12 0
A104 B 0 0 1103B 20
A104 B 0 0 1412B 6
A726 B 0 0 0902A 4
A946 A 201 5 0
B212 A 203 7 0
B212 A 210 4 0
B212 A 211 4 0
B212 B 0 0 0312C 8
B212 B 0 0 0404B 8
B212 B 0 0 0411C 6
–Ted
|
