[thelist] combining two similar sql queries into one
Jeremy Weiss
eccentric.one at gmail.com
Fri Sep 18 10:19:16 CDT 2009
I'm trying to build a report that will display all parts and services
on a certain workorder, ordered alphabetically by vendor name. I have
a query to do just parts and the query to do just services, but I'm
having trouble trying to combine the two. For what it's worth, I'm
querying an Oracle db.
Here's the parts query:
SELECT
CORR.WORKORDERS.WORKORDERNUMBER,
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_MASTER.FORMATTEDPURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_MASTER.VENDORNAME,
CORR.PURCHASE_ORDERS_COMMON.LINEITEMNUMBER,
CORR.PURCHASE_ORDERS_COMMON.PARTNUMBER,
CORR.PURCHASE_ORDERS_COMMON.PARTDESCRIPTION,
CORR.PURCHASE_ORDERS_COMMON.ORDERDATE,
CORR.PURCHASE_ORDERS_COMMON.PURCHASEPRICE,
CORR.PURCHASE_ORDERS_COMMON.FORMATTEDPURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_PROCURE.QUANTITYPURCHASED
FROM
CORR.PURCHASE_ORDERS_COMMON
Inner Join CORR.PURCHASE_ORDERS_MASTER ON
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERMASTERKEY =
CORR.PURCHASE_ORDERS_COMMON.PURCHASEORDERMASTERKEY
Inner Join CORR.WORKORDERS ON
CORR.PURCHASE_ORDERS_COMMON.WORKORDERKEY =
CORR.WORKORDERS.WORKORDERKEY
Inner Join CORR.PURCHASE_ORDERS_PROCURE ON
CORR.PURCHASE_ORDERS_COMMON.PURCHASEORDERCOMMONKEY =
CORR.PURCHASE_ORDERS_PROCURE.PURCHASEORDERCOMMONKEY
WHERE
CORR.WORKORDERS.WORKORDERNUMBER='9113'
ORDER BY
CORR.PURCHASE_ORDERS_MASTER.VENDORNAME,
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_COMMON.LINEITEMNUMBER
Here's the services query:
SELECT
CORR.WORKORDERS.WORKORDERNUMBER AS WO2,
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_MASTER.FORMATTEDPURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_MASTER.VENDORNAME,
CORR.PURCHASE_ORDERS_COMMON.LINEITEMNUMBER,
CORR.PURCHASE_ORDERS_COMMON.ORDERDATE,
CORR.PURCHASE_ORDERS_COMMON.PURCHASEPRICE,
CORR.PURCHASE_ORDERS_COMMON.FORMATTEDPURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_SERVICE.QUANTITYTOSEND,
CORR.PURCHASE_ORDERS_SERVICE.SERVICEDESCRIPTION
FROM
CORR.PURCHASE_ORDERS_COMMON
Inner Join CORR.PURCHASE_ORDERS_MASTER ON
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERMASTERKEY =
CORR.PURCHASE_ORDERS_COMMON.PURCHASEORDERMASTERKEY
Inner Join CORR.WORKORDERS ON
CORR.PURCHASE_ORDERS_COMMON.WORKORDERKEY =
CORR.WORKORDERS.WORKORDERKEY
Inner Join CORR.PURCHASE_ORDERS_SERVICE ON
CORR.PURCHASE_ORDERS_COMMON.PURCHASEORDERCOMMONKEY =
CORR.PURCHASE_ORDERS_SERVICE.PURCHASEORDERCOMMONKEY
WHERE
CORR.WORKORDERS.WORKORDERNUMBER='9113'
ORDER BY
CORR.PURCHASE_ORDERS_MASTER.VENDORNAME,
CORR.PURCHASE_ORDERS_MASTER.PURCHASEORDERNUMBER,
CORR.PURCHASE_ORDERS_COMMON.LINEITEMNUMBER
As you can see, the two queries are quite similar. But my attempts at
merging the two have ended in everything from 0 records to hundreds of
thousands of records and it should only be about 1500 for this
particular work order.
Thanks,
Jeremy
More information about the thelist
mailing list