SQL union between tables with different column names

In the example below we have created 2 tables is Db2 AS400 using DDS but that really doesn't matter. You can use this example for any database tables created using Create table statement or any other way.

In my project I had to query the orders by order date and then sort them with the latest order date showing up first. The issue was when an Order got Invoiced it went into the history file which was kind of similar but had different field names. So the solution was to create a view using SQL union between the two tables and do your query on a single view. Here is how ...

Open Order Table

Field              File               Type                 Length  Scale
OID                OPENO              NUMERIC                  10       
OCUST              OPENO              CHARACTER                10       
ONAME              OPENO              CHARACTER                30       
ODATE              OPENO              NUMERIC                   8       
OAMOUNT            OPENO              NUMERIC                  11     2  

History Order Table

Field              File               Type                 Length  Scale
HID                HISTO              NUMERIC                  10       
HCUST              HISTO              CHARACTER                10       
HNAME              HISTO              CHARACTER                30       
HDATE              HISTO              NUMERIC                   8       
HAMOUNT            HISTO              NUMERIC                  11     2 

Union between the Open and History tables

Create view MyOrders as (                                
select oid, ocust, oname, odate, oamount                 
from openo                                               
union all                                                
select hid oid, hcust ocust, hname oname, hdate odate,  hamount oamount                                          
from histo)      

Now its easy to query the MyOrders view with any selection criteria or order by ...
Select * from MyOrders where odate between 20120222 and 20120223

No comments:

Post a Comment

NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.