Problem combining UNION and OVER

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem combining UNION and OVER

Author

Topic

VorTechS
Starting Member

2 Posts

Posted - 2013-11-12 : 05:09:32

Hi All,

First time here, and in need of some major help as I've hit my limit of knowledge!

I have the following query, which is throwing up the error:

quote:

Msg 205, Level 16, State 1, Line 5
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.


declare @productTypeId int = 1150
declare @productId int = 867417
declare @matchCutOff int = 70

SELECT TOP(7) * FROM (SELECT RowId=ROW_NUMBER() OVER (ORDER BY
					(SELECT CAST(COUNT(*) AS FLOAT) FROM (SELECT paOriginal2.pattr_attr_id, paOriginal2.pattr_value
FROM Product_Attribute paOriginal2 
WHERE paOriginal2.pattr_prod_id = @productId
INTERSECT
SELECT paReplacement.pattr_attr_id, paReplacement.pattr_value
FROM Product_Attribute paReplacement
INNER JOIN product pdReplace ON pdReplace.prod_id=paReplacement.pattr_prod_id
WHERE pdReplace.prod_id=p.prod_id) AS Matching) / (SELECT COUNT(paOriginal.pattr_attr_id) FROM Product_Attribute paOriginal WHERE paOriginal.pattr_prod_id=@productId) * 100 desc,
                    prod_year desc,
					p.prod_rrp,
					pc.pcat_name,
					pt.ptype_name,
					m.man_name,
					p.prod_model), 
					p.prod_id,
					p.prod_name,
					p.prod_model,
					p.prod_year,
					p.prod_rrp,
					p.prod_desc,
					p.prod_img_name,
					p.prod_current,
					pc.pcat_id,
					pt.ptype_id,
					pc.pcat_name,
					pt.ptype_name,
					m.man_name,
					m.man_priority_brand,
					100 AS matchquality,
					p.prod_man_id,
                    TotalRows=Count(p.prod_id) OVER()
FROM Product_Replacement prep
INNER JOIN product p ON p.prod_id = prep.prep_prod_id2
INNER JOIN	manufacturer m ON p.prod_man_id = m.man_id
INNER JOIN	ProductType pt ON p.prod_ptype_id = pt.ptype_id
INNER JOIN	ProductCategory pc ON pt.ptype_pcat_id = pc.pcat_id
LEFT JOIN   Product_Attribute pa ON pa.pattr_prod_id = p.prod_id
INNER JOIN	Manufacturer_ProductType mpt on m.man_id = mpt.manptype_man_id AND pt.ptype_id = mpt.manptype_ptype_id
WHERE prep.prep_prod_id1 = @productId
					UNION SELECT
					p.prod_id,
					p.prod_name,
					p.prod_model,
					p.prod_year,
					p.prod_rrp,
					p.prod_desc,
					p.prod_img_name,
					p.prod_current,
					pc.pcat_id,
					pt.ptype_id,
					pc.pcat_name,
					pt.ptype_name,
					m.man_name,
                    m.man_priority_brand,
					(SELECT CAST(COUNT(*) AS FLOAT) FROM (SELECT paOriginal2.pattr_attr_id, paOriginal2.pattr_value
FROM Product_Attribute paOriginal2 
WHERE paOriginal2.pattr_prod_id = @productId
INTERSECT
SELECT paReplacement.pattr_attr_id, paReplacement.pattr_value
FROM Product_Attribute paReplacement
INNER JOIN product pdReplace ON pdReplace.prod_id=paReplacement.pattr_prod_id
WHERE pdReplace.prod_id=p.prod_id) AS Matching) / (SELECT COUNT(paOriginal.pattr_attr_id) FROM Product_Attribute paOriginal WHERE paOriginal.pattr_prod_id=@productId) * 100 AS matchquality,
                    p.prod_man_id,
                    TotalRows=Count(p.prod_id) OVER()
		FROM        Product p
		INNER JOIN	manufacturer m ON p.prod_man_id = m.man_id
		INNER JOIN	ProductType pt ON p.prod_ptype_id = pt.ptype_id
		INNER JOIN	ProductCategory pc ON pt.ptype_pcat_id = pc.pcat_id
        LEFT JOIN   Product_Attribute pa ON pa.pattr_prod_id = p.prod_id
		INNER JOIN	Manufacturer_ProductType mpt on m.man_id = mpt.manptype_man_id AND pt.ptype_id = mpt.manptype_ptype_id
		WHERE p.prod_ptype_id=@productTypeId AND p.prod_year >= ((SELECT prod_year FROM product op WHERE op.prod_id = @productId) - 2) AND p.prod_variant=0 AND p.prod_hidden=0 AND ISNULL(p.prod_rrp,0) > 0
		AND m.man_priority_brand = (SELECT m2.man_priority_brand FROM product op2 INNER JOIN Manufacturer m2 ON m2.man_id = op2.prod_man_id WHERE op2.prod_id = @productId)		
		AND (SELECT count(pr.prep_id) FROM Product_Replacement pr WHERE pr.prep_prod_id1 = p.prod_id) = 0
		AND ((SELECT CAST(COUNT(*) AS FLOAT) FROM (SELECT paOriginal2.pattr_attr_id, paOriginal2.pattr_value
FROM Product_Attribute paOriginal2 
WHERE paOriginal2.pattr_prod_id = @productId
INTERSECT
SELECT paReplacement.pattr_attr_id, paReplacement.pattr_value
FROM Product_Attribute paReplacement
INNER JOIN product pdReplace ON pdReplace.prod_id=paReplacement.pattr_prod_id
WHERE pdReplace.prod_id=p.prod_id) AS Matching) / (SELECT COUNT(paOriginal.pattr_attr_id) FROM Product_Attribute paOriginal WHERE paOriginal.pattr_prod_id=@productId) * 100) >= @matchCutOff
        AND (SELECT count(sp_id) FROM SupplierPrice sp WHERE sp.sp_prod_id = p.prod_id AND DATEDIFF(DAY, sp.sp_date_updated,GETDATE()) <= 60) > 0
		GROUP BY	p.prod_id,
					p.prod_name,
					p.prod_model,
					p.prod_model_search,
					p.prod_year,
					p.prod_rrp,
					p.prod_desc,
					p.prod_img_name,
					p.prod_current,
					pc.pcat_id,
					pt.ptype_id,
					pc.pcat_name,
					pt.ptype_name,
					m.man_name,
					m.man_priority_brand, p.prod_man_id) Results WHERE  RowId > 0

I'm probably missing something obvious here, but to me both statements either side of the UNION have the same target list fields?

If I take one of the target fields out of the statement before the union, the statement will compile and run (albeit with a CAST error because of a field that is not meant to be being casted).

Can anyone help me identify why the target field lists are different and suggest a way to fix it?

Ideally the end result should be both statements either side of the UNION should return results, of which the TOP 7 are selected based on the current page (where ROWS > x)

If this can be solved without a re-write, that would be great (because of time pressure), I would then be interested in learning a better way of achieving the same result, as I am guessing there is a much better way of doing this!

Many thanks!

VorTechS
Starting Member

2 Posts

Posted - 2013-11-12 : 05:21:04

Okay, typically after bashing it all out on here I now understand why it's broken - but as yet not how to fix it.

quote:

SELECT RowId=ROW_NUMBER() OVER

...would need to be present on both sides of the UNION which it currently isn't. That of course would be wrong though, because the ROW_NUMBER would need to be part of the overall statement, not applied to each side of the UNION.

So the problem is now, how do I write this query to work the way I need it to?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-12 : 05:34:23

use derived table


select RowID = row_number() ....
from
(
     <query 1>
     union 
     <query 2>
) as D

or CTE

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources