Using UNION ALL to combine several queries

[stewpyd]

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:

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?

[cutepinkbunnies]

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'.

[stewpyd]

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

[cutepinkbunnies]

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:

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

[tesuji]

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:

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

That orders second column (b,y).

krs,
tesu