[thelist] combining two similar sql queries into one
r937
rudy at r937.com
Fri Sep 18 13:17:56 CDT 2009
hi jeremy
a UNION would be one way to "merge" the queries
(presumably you cannot just add the additional join to
purchase_orders_service into the first query because of cross join effects
resulting from multiple purchase_orders_procure rows and/or multiple
purchase_orders_service rows)
care must be taken to align the SELECT clause columns properly, and this
requires the use of some NULL placeholders
SELECT wo.workordernumber
, pom.purchaseordernumber
, pom.formattedpurchaseordernumber
, pom.vendorname
, poc.lineitemnumber
, poc.partnumber
, poc.partdescription
, poc.orderdate
, poc.purchaseprice
, poc.formattedpurchaseordernumber
, pop.quantitypurchased
, NULL quantitytosend
, NULL servicedescription
FROM corr.workorders wo
INNER
JOIN corr.purchase_orders_common poc
ON poc.workorderkey = wo.workorderkey
INNER
JOIN corr.purchase_orders_master pom
ON pom.purchaseordermasterkey = poc.purchaseordermasterkey
INNER
JOIN corr.purchase_orders_procure pop
ON pop.purchaseordercommonkey = poc.purchaseordercommonkey
WHERE wo.workordernumber='9113'
UNION ALL
SELECT wo.workordernumber
, pom.purchaseordernumber
, pom.formattedpurchaseordernumber
, pom.vendorname
, poc.lineitemnumber
, NULL
, NULL
, poc.orderdate
, poc.purchaseprice
, poc.formattedpurchaseordernumber
, NULL
, pos.quantitytosend
, pos.servicedescription
FROM corr.workorders wo
INNER
JOIN corr.purchase_orders_common poc
ON poc.workorderkey = wo.workorderkey
INNER
JOIN corr.purchase_orders_master pom
ON pom.purchaseordermasterkey = poc.purchaseordermasterkey
INNER
JOIN corr.purchase_orders_service pos
ON pos.purchaseordercommonkey = poc.purchaseordercommonkey
WHERE wo.workordernumber='9113'
ORDER
BY 4 -- vendorname
, 2 -- purchaseordernumber
, 5 -- lineitemnumber
i'm not sure if the ORDER BY clause is valid like this in oracle, so if
there are any problems with the syntax, let me know
rudy
http://simply-sql.com/
More information about the thelist
mailing list