[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