Case Statement and Union statement

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 2012 Forums

Transact-SQL (2012)

Case Statement and Union statement

Author

Topic

theladycoder
Starting Member

16 Posts

Posted - 2014-04-30 : 11:32:02

I have the following query:
SELECT keytype = 'so' --sales order
,keyid = salesid
,site = ViewName.inventsiteid
,ViewName.dataareaid AS dataareaid
FROM ViewName

UNION ALL

SELECT keytype = 'to' --transfer order
,keyid = TableName1.transferid
,site = TableName2.INVENTLOCATIONIDTRANSIT
,TableName1.dataareaid AS dataareaid
FROM TableName1
LEFT JOIN TableName2 ON TableName2.TRANSFERID = TableName1.TRANSFERID

What I need to do is build a case statement on the keytype. If keytype = 'so' then grab only that data, if it = 'to' then only grab that data. Not sure how to do that here.

Thanks in advance.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-04-30 : 12:25:09

Are you saying that within each of those data sets there might be a key that is the same (KeyID) and that you only want one row with said Key? Furthermore, precedence should be given to the row/dataset where the KeyType is equal to "so"?

If that is the case, then here is one way:

SELECT 
	*
FROM
	(
		SELECT
			*
			,ROW_NUMBER() OVER (PARTITION BY KeyID ORDER BY CASE WHEN KeyType = 'so' THEN 0 ELSE 1 END) AS RowNum
		FROM
			(
				SELECT 
					keytype = 'so' --sales order
					,keyid = salesid
					,site = ViewName.inventsiteid
					,ViewName.dataareaid AS dataareaid
				FROM 
					ViewName

				UNION ALL

				SELECT 
					keytype = 'to' --transfer order
					,keyid = TableName1.transferid
					,site = TableName2.INVENTLOCATIONIDTRANSIT
					,TableName1.dataareaid AS dataareaid
				FROM 
					TableName1
				LEFT JOIN 
					TableName2 
					ON TableName2.TRANSFERID = TableName1.TRANSFERID
			) AS A
	) AS B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources