Combining Queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Mike Perry (_at_)
Date: 07/20/04 

Date: Tue, 20 Jul 2004 13:49:28 -0500

OK, I give up, there has to be a simple answer to this but I've dented the
wall banging my head into it trying to figure it out.

The following Query works as expected and the result set is what is desired
(13338 records):

SELECT *
FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
   Order_Nbr,
   Entry_Date,
   Invoice_Nbr,
   Line_Nbr,
   Item_Nbr,
   Price,
   Qty_Ord,
   Price_Extension
   FROM oehistdetail') AS wcw,
 (SELECT C1.U_Key3 AS Cust_Nbr
 FROM Contact2 AS C2,
  Contact1 AS C1,
  OPENQUERY(wcw, 'SELECT Cust_Nbr
    FROM Customers') AS Custs
 WHERE C2.AccountNo=C1.AccountNo
 AND C2.UVQ2CLASSI = 'Business Growth'
 AND C1.U_Key3 <> ''
 AND C1.U_Key3=Custs.Cust_Nbr) AS GrowthCusts
WHERE (entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
 AND UPPER(RIGHT(Item_Nbr, 4)) <> 'RNDM'
 AND Price_Extension <> 0
 AND Item_Nbr <> '@FREIGHT'
 AND wcw.Cust_Nbr=GrowthCusts.Cust_Nbr)
ORDER BY Entry_Date, Order_Nbr

And the following also returns what is desired (1027 records):

SELECT *
FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
   Order_Nbr,
   Entry_Date,
   Invoice_Nbr,
   Line_Nbr,
   Item_Nbr,
   Price,
   Qty_Ord,
   Price_Extension
   FROM oehistdetail')
WHERE (entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
 AND UPPER(RIGHT(Item_Nbr, 4)) = 'RNDM')
ORDER BY Entry_Date, Order_Nbr

But when I combine these two queries by adding the last two lines as follows
I don't get the expected 14,365 records, instead I get several million with
duplicates:

SELECT *
FROM OPENQUERY(wcw, 'SELECT Cust_Nbr,
   Order_Nbr,
   Entry_Date,
   Invoice_Nbr,
   Line_Nbr,
   Item_Nbr,
   Price,
   Qty_Ord,
   Price_Extension
   FROM oehistdetail') AS wcw,
 (SELECT C1.U_Key3 AS Cust_Nbr
 FROM Contact2 AS C2,
  Contact1 AS C1,
  OPENQUERY(wcw, 'SELECT Cust_Nbr
    FROM Customers') AS Custs
 WHERE C2.AccountNo=C1.AccountNo
 AND C2.UVQ2CLASSI = 'Business Growth'
 AND C1.U_Key3 <> ''
 AND C1.U_Key3=Custs.Cust_Nbr) AS GrowthCusts
WHERE ((entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
 AND UPPER(RIGHT(Item_Nbr, 4)) <> 'RNDM'
 AND Price_Extension <> 0
 AND Item_Nbr <> '@FREIGHT'
 AND wcw.Cust_Nbr=GrowthCusts.Cust_Nbr))
 OR ((entry_date >= CONVERT(DATETIME, '2004-01-01', 102)
 AND UPPER(RIGHT(Item_Nbr, 4)) = 'RNDM'))
ORDER BY Entry_Date, Order_Nbr

Basically what I have to do is arrive at a result set that includes all the
order detail records which were purchased by customers that are tagged as
"Business Growth" (which could also include "random" sales) and all "random"
sales whether they are "business growth" tagged or not since the first of
the year.

I know that the OPENQUERY portions appear to be less than optimized, but in
some cases the only way I can get the result set I want from the Linked
Server (an ODBC driver connection to a COBOL dataset on a Linux box) is by
querying the entire dataset and then apply constraints once the SQL server
has the dataset from the Linked Server (for instance, UPPER and RIGHT are
not recognized by the ODBC driver).

The Contact1 and Contact2 tables are in a Database called "Windy" and the
order detail data is on the Linked Server which is referred to as "wcw."

Any help would be greatly appreciated!

Thanks in advance.

Mike