[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