Distinguishing records from which select statement using UNION in SQL

[lsmeteor]

Hello all,
I just found the perfect sql statement (gives me what I am loking for) except for one little detail.
I am querying the order and invoice tables. I can't differientiate between records and see where they came from.
I am using Pervasive 2000i SP4.
Here is the sql statement used:

code:

SELECT Ord_Order_No, Ord_Order_Date, Ord_Cust_No
FROM ORDERS1
WHERE Ord_Rec_No = ' ' AND
(Ord_Order_No BETWEEN ' ' AND 'ZZZZZZ')
AND ORD_CUST_PO_NO = '1234'
UNION
SELECT Hi_Invoice_No as Ord_Order_No, Hi_Invoice_Date as Ord_Order_Date, Hi_Cust_No as Ord_Cust_No
FROM SALESHIST1 WHERE HI_CUST_PO_NO = '1234' AND
HI_TYPE_KEY = 'I' AND
HI_INVOICE_NO between ' ' and 'ZZZZZZ' AND HI_REC_NO = '000'



It just returns 3 columns as expected.

Here is what it looks like:

code:
ORD_ORDER_NO ORD_ORDER_DATE ORD_CUST_NO
------------ -------------- -----------
100569 19990507 CZYARN
700293 19990507 CZYARN
700397 19991125 GEOKON



I would like to add a column, say, TYPE, that would be 'O' for an order or 'I' for an invoice so that it would look like this:

code:ORD_ORDER_NO ORD_ORDER_DATE ORD_CUST_NO TYPE
------------ -------------- ----------- ----
100569 19990507 CZYARN I
700293 19990507 CZYARN O
700397 19991125 GEOKON O


Can that be done?
TIA

 

[lsmeteor]

I just got my solution:

SELECT Ord_Order_No, Ord_Order_Date, Ord_Cust_No, 'O' as Type
FROM ORDERS1
WHERE Ord_Rec_No = ' ' AND
(Ord_Order_No BETWEEN ' ' AND 'ZZZZZZ')
AND ORD_CUST_PO_NO = '1234'
UNION
SELECT Hi_Invoice_No as Ord_Order_No, Hi_Invoice_Date as Ord_Order_Date, Hi_Cust_No as Ord_Cust_No, 'I' as Type
FROM SALESHIST1 WHERE HI_CUST_PO_NO = '1234' AND
HI_TYPE_KEY = 'I' AND
HI_INVOICE_NO between ' ' and 'ZZZZZZ' AND HI_REC_NO = '000'

This works great!!