Using UNION ALL to combine several queries

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 402,050 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,464 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MS SQL advertiser: Programming Forums

Thread Tools Display Modes

Views: 666 | Replies: 4

•

Join Date: Jul 2008

Posts: 3

Reputation: stewpyd is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

stewpyd

Offline

Newbie Poster

Using UNION ALL to combine several queries

Jul 17th, 2008

Hi,

I have an average cost greater than price crystal report for 20 different stores. I want to create a master report that lists all of the results over the entire company. Hence, I want to use union all in a SQL command for this master crystal report.

For example, combining too reports with store databases of TRAD001 and TRAD002:

Help with Code Tags

(Toggle Plain Text)

SELECT c.[description] AS 'Group'
, i.[description] AS 'Description'
, i.part_no AS 'Part No'
, TRAD001.dbo.PBS_fnQuantityInStock(il.part_no, il.location) AS 'In Stock'
, il.po_uom AS 'Purchasing UOM'
, i.vendor AS 'Preferred Vendor'
, vq.last_price AS 'Preferred Vendor Quote'
, (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) AS 'Highest Nonpreferred Vendor Quote'
, il.avg_cost AS 'Average Cost'
, pp.price_a AS 'Retail Price'
, CASE
WHEN vq.last_price > pp.price_a
THEN 'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 'PO UOM may be wrong'
ELSE 'Unknown'
END AS 'Problem'
FROM TRAD001.dbo.inv_master i
INNER JOIN TRAD001.dbo.inv_list il ON il.part_no = i.part_no
INNER JOIN TRAD001.dbo.part_price pp ON i.part_no = pp.part_no
LEFT JOIN TRAD001.dbo.category c ON c.kys = i.category
LEFT JOIN TRAD001.dbo.vendor_sku vq ON vq.sku_no = i.part_no AND vq.vendor_no = i.vendor
WHERE i.void <> 'V' AND i.obsolete <> 1 -- Items that are not void or obsolete
AND pp.price_a > 0 -- Items where the retail price is greater than zero
AND il.avg_cost >= 1.10 * pp.price_a -- Items where the average cost is more than 10% higher than the retail price
AND i.category <> '02' -- Excluding dispensary items
and i.status = 'P' -- Purchase items only
and TRAD001.dbo.PBS_fnQuantityInStock(il.part_no, il.location) > 0 -- Items that are in stock
ORDER BY CASE
WHEN vq.last_price > pp.price_a
THEN 1 --'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 2 --'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 3 --'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 4 --'PO UOM may be wrong'
ELSE 5 --'Unknown'
END
, c.[description]
, i.[description]

go
union all

SELECT c.[description] AS 'Group'
, i.[description] AS 'Description'
, i.part_no AS 'Part No'
, TRAD002.dbo.PBS_fnQuantityInStock(il.part_no, il.location) AS 'In Stock'
, il.po_uom AS 'Purchasing UOM'
, i.vendor AS 'Preferred Vendor'
, vq.last_price AS 'Preferred Vendor Quote'
, (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) AS 'Highest Nonpreferred Vendor Quote'
, il.avg_cost AS 'Average Cost'
, pp.price_a AS 'Retail Price'
, CASE
WHEN vq.last_price > pp.price_a
THEN 'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 'PO UOM may be wrong'
ELSE 'Unknown'
END AS 'Problem'
FROM TRAD002.dbo.inv_master i
INNER JOIN TRAD002.dbo.inv_list il ON il.part_no = i.part_no
INNER JOIN TRAD002.dbo.part_price pp ON i.part_no = pp.part_no
LEFT JOIN TRAD002.dbo.category c ON c.kys = i.category
LEFT JOIN TRAD002.dbo.vendor_sku vq ON vq.sku_no = i.part_no AND vq.vendor_no = i.vendor
WHERE i.void <> 'V' AND i.obsolete <> 1 -- Items that are not void or obsolete
AND pp.price_a > 0 -- Items where the retail price is greater than zero
AND il.avg_cost >= 1.10 * pp.price_a -- Items where the average cost is more than 10% higher than the retail price
AND i.category <> '02' -- Excluding dispensary items
and i.status = 'P' -- Purchase items only
and TRAD002.dbo.PBS_fnQuantityInStock(il.part_no, il.location) > 0 -- Items that are in stock
ORDER BY CASE
WHEN vq.last_price > pp.price_a
THEN 1 --'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 2 --'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 3 --'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 4 --'PO UOM may be wrong'
ELSE 5 --'Unknown'
END
, c.[description]
, i.[description]

SELECT c.[description] AS 'Group'
, i.[description] AS 'Description'
, i.part_no AS 'Part No'
, TRAD001.dbo.PBS_fnQuantityInStock(il.part_no, il.location) AS 'In Stock'
, il.po_uom AS 'Purchasing UOM'
, i.vendor AS 'Preferred Vendor'
, vq.last_price AS 'Preferred Vendor Quote'
, (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) AS 'Highest Nonpreferred Vendor Quote'
, il.avg_cost AS 'Average Cost'
, pp.price_a AS 'Retail Price'
, CASE
WHEN vq.last_price > pp.price_a
THEN 'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 'PO UOM may be wrong'
ELSE 'Unknown'
END AS 'Problem'
FROM TRAD001.dbo.inv_master i
INNER JOIN TRAD001.dbo.inv_list il ON il.part_no = i.part_no
INNER JOIN TRAD001.dbo.part_price pp ON i.part_no = pp.part_no
LEFT JOIN TRAD001.dbo.category c ON c.kys = i.category
LEFT JOIN TRAD001.dbo.vendor_sku vq ON vq.sku_no = i.part_no AND vq.vendor_no = i.vendor
WHERE i.void <> 'V' AND i.obsolete <> 1 -- Items that are not void or obsolete
AND pp.price_a > 0 -- Items where the retail price is greater than zero
AND il.avg_cost >= 1.10 * pp.price_a -- Items where the average cost is more than 10% higher than the retail price
AND i.category <> '02' -- Excluding dispensary items
and i.status = 'P' -- Purchase items only
and TRAD001.dbo.PBS_fnQuantityInStock(il.part_no, il.location) > 0 -- Items that are in stock
ORDER BY CASE
WHEN vq.last_price > pp.price_a
THEN 1 --'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD001.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 2 --'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 3 --'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 4 --'PO UOM may be wrong'
ELSE 5 --'Unknown'
END
, c.[description]
, i.[description]

go
union all

SELECT c.[description] AS 'Group'
, i.[description] AS 'Description'
, i.part_no AS 'Part No'
, TRAD002.dbo.PBS_fnQuantityInStock(il.part_no, il.location) AS 'In Stock'
, il.po_uom AS 'Purchasing UOM'
, i.vendor AS 'Preferred Vendor'
, vq.last_price AS 'Preferred Vendor Quote'
, (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) AS 'Highest Nonpreferred Vendor Quote'
, il.avg_cost AS 'Average Cost'
, pp.price_a AS 'Retail Price'
, CASE
WHEN vq.last_price > pp.price_a
THEN 'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 'PO UOM may be wrong'
ELSE 'Unknown'
END AS 'Problem'
FROM TRAD002.dbo.inv_master i
INNER JOIN TRAD002.dbo.inv_list il ON il.part_no = i.part_no
INNER JOIN TRAD002.dbo.part_price pp ON i.part_no = pp.part_no
LEFT JOIN TRAD002.dbo.category c ON c.kys = i.category
LEFT JOIN TRAD002.dbo.vendor_sku vq ON vq.sku_no = i.part_no AND vq.vendor_no = i.vendor
WHERE i.void <> 'V' AND i.obsolete <> 1 -- Items that are not void or obsolete
AND pp.price_a > 0 -- Items where the retail price is greater than zero
AND il.avg_cost >= 1.10 * pp.price_a -- Items where the average cost is more than 10% higher than the retail price
AND i.category <> '02' -- Excluding dispensary items
and i.status = 'P' -- Purchase items only
and TRAD002.dbo.PBS_fnQuantityInStock(il.part_no, il.location) > 0 -- Items that are in stock
ORDER BY CASE
WHEN vq.last_price > pp.price_a
THEN 1 --'Preferred vendor quote > retail price'
WHEN (SELECT MAX(last_price) FROM TRAD002.dbo.vendor_sku WHERE sku_no = i.part_no AND vendor_no <> i.vendor) > pp.price_a
THEN 2 --'Non-preferred vendor quote > retail price'
WHEN vq.last_price IS NULL
THEN 3 --'No preferred vendor quote exists'
WHEN il.po_uom = i.uom
THEN 4 --'PO UOM may be wrong'
ELSE 5 --'Unknown'
END
, c.[description]
, i.[description]

Gives me the results of the first report but not the second. "Invalid syntax near the keyword union." I think this has something to do with the order by statement, because if I remove this, it works fine by is obviously not ordered. Any ideas?

Last edited by Tekmaven : Jul 18th, 2008 at 5:39 am. Reason: Code tags

•

Join Date: Mar 2006

Location: East Amherst, NY

Posts: 102

Reputation: cutepinkbunnies is an unknown quantity at this point

Rep Power: 3

Solved Threads: 6

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Using UNION ALL to combine several queries

Jul 22nd, 2008

The invalid syntax is coming from the 'go' you have in between the union. 'go' is making SQL go do its work and it starts the next command with 'union all' and is probably wondering what to union.

Also, I'm not sure if 'union all' is ok either (although it may be the proper syntax) I've always just used 'union'.

•

Join Date: Jul 2008

Posts: 3

Reputation: stewpyd is an unknown quantity at this point

Rep Power: 0

Solved Threads: 0

stewpyd

Offline

Newbie Poster

Re: Using UNION ALL to combine several queries

Jul 22nd, 2008

Still doesn't work when I remove Go and change union all to just union.

•

Join Date: Mar 2006

Location: East Amherst, NY

Posts: 102

Reputation: cutepinkbunnies is an unknown quantity at this point

Rep Power: 3

Solved Threads: 6

cutepinkbunnies cutepinkbunnies is offline

Offline

Junior Poster

Re: Using UNION ALL to combine several queries

Jul 22nd, 2008

Ok...If this is working without the order by try this.

1. Remove the order by on both SQL statements
2. Turn the unioned set into a sub statement...something like:

Help with Code Tags

(Toggle Plain Text)

Select * from 
     (Select * from table
      union
      Select * from table)
order by <use fields from sub-select>

Last edited by Tekmaven : Jul 22nd, 2008 at 6:43 pm. Reason: Code tags

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: Using UNION ALL to combine several queries

Jul 22nd, 2008

hello,

in union clause the column which should be ordered cannot be denoted by column name. Column to be ordered must be specified by its position number like in:

Help with Code Tags

(Toggle Plain Text)

select a, b, c  from t1
union
select x, y, z from t2
order by 2

That orders second column (b,y).

krs,
tesu

Last edited by Tekmaven : Jul 22nd, 2008 at 6:52 pm. Reason: Code tags

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.