CASE multiple - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

CASE multiple

Author

Topic

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-02 : 22:43:55

hi guys,

another question for today (maybe another silly q :P)..
can somebody shorten my case statement :-

i want to have NULL for RUdf1 til RUdf10 columns and CUdf1 til CUdf10 if it follow condition "select vblablaba'

currently im doing like below, that will take 20 CASE statement in my select statement!

CASE WHEN exists (select p.picktype from tblItemPickFormat p
where p.ItemClientRef=r.ItemClientRef AND (p.PickType=c.ClientUDF1) ) THEN RecvUDf1
ELSE NULL END AS RecvUDF1, --this one until 10

CASE WHEN exists (select p.picktype from tblItemPickFormat p
where p.ItemClientRef=r.ItemClientRef AND (p.PickType=c.ClientUDF1) ) THEN ClientUDf1
ELSE NULL END AS ClientUDF1, --this one until 10

so total 20.. phewwww... helppppp

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-02 : 22:52:44

Instead of writing correlated subquery each time inside CASE, why don't you go for LEFT JOIN?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-02 : 22:58:06

Since you have 20 columns, you must check every column.
I believe this will perform much faster. Only 10 JOINS now, where there where 20 subqueries before.

SELECT DISTINCT	CASE WHEN p1.ItemClientRef IS NULL THEN NULL ELSE RecvUDf1 END AS RecvUDf1,
		CASE WHEN p1.ItemClientRef IS NULL THEN NULL ELSE ClientUDf1 END AS ClientUDf1,
		CASE WHEN p2.ItemClientRef IS NULL THEN NULL ELSE RecvUDf2 END AS RecvUDf2,
		CASE WHEN p2.ItemClientRef IS NULL THEN NULL ELSE ClientUDf2 END AS ClientUDf2,
		CASE WHEN p3.ItemClientRef IS NULL THEN NULL ELSE RecvUDf3 END AS RecvUDf3,
		CASE WHEN p3.ItemClientRef IS NULL THEN NULL ELSE ClientUDf3 END AS ClientUDf3,
		CASE WHEN p4.ItemClientRef IS NULL THEN NULL ELSE RecvUDf4 END AS RecvUDf4,
		CASE WHEN p4.ItemClientRef IS NULL THEN NULL ELSE ClientUDf4 END AS ClientUDf4,
		CASE WHEN p5.ItemClientRef IS NULL THEN NULL ELSE RecvUDf5 END AS RecvUDf5,
		CASE WHEN p5.ItemClientRef IS NULL THEN NULL ELSE ClientUDf5 END AS ClientUDf5,
		CASE WHEN p6.ItemClientRef IS NULL THEN NULL ELSE RecvUDf6 END AS RecvUDf6,
		CASE WHEN p6.ItemClientRef IS NULL THEN NULL ELSE ClientUDf6 END AS ClientUDf6,
		CASE WHEN p7.ItemClientRef IS NULL THEN NULL ELSE RecvUDf7 END AS RecvUDf7,
		CASE WHEN p7.ItemClientRef IS NULL THEN NULL ELSE ClientUDf7 END AS ClientUDf7,
		CASE WHEN p8.ItemClientRef IS NULL THEN NULL ELSE RecvUDf8 END AS RecvUDf8,
		CASE WHEN p8.ItemClientRef IS NULL THEN NULL ELSE ClientUDf8 END AS ClientUDf8,
		CASE WHEN p9.ItemClientRef IS NULL THEN NULL ELSE RecvUDf9 END AS RecvUDf9,
		CASE WHEN p9.ItemClientRef IS NULL THEN NULL ELSE ClientUDf9 END AS ClientUDf9,
		CASE WHEN p10.ItemClientRef IS NULL THEN NULL ELSE RecvUDf10 END AS RecvUDf10,
		CASE WHEN p10.ItemClientRef IS NULL THEN NULL ELSE ClientUDf10 END AS ClientUDf10
FROM		<YourTableNameHere> AS t
LEFT JOIN	tblItemPickFormat AS p1 ON p1.ItemClientRef = t.ItemClientRef AND p1.PickType = t.ClientUDF1
LEFT JOIN	tblItemPickFormat AS p2 ON p2.ItemClientRef = t.ItemClientRef AND p2.PickType = t.ClientUDF2
LEFT JOIN	tblItemPickFormat AS p3 ON p3.ItemClientRef = t.ItemClientRef AND p3.PickType = t.ClientUDF3
LEFT JOIN	tblItemPickFormat AS p4 ON p4.ItemClientRef = t.ItemClientRef AND p4.PickType = t.ClientUDF4
LEFT JOIN	tblItemPickFormat AS p5 ON p5.ItemClientRef = t.ItemClientRef AND p5.PickType = t.ClientUDF5
LEFT JOIN	tblItemPickFormat AS p6 ON p6.ItemClientRef = t.ItemClientRef AND p6.PickType = t.ClientUDF6
LEFT JOIN	tblItemPickFormat AS p7 ON p7.ItemClientRef = t.ItemClientRef AND p7.PickType = t.ClientUDF7
LEFT JOIN	tblItemPickFormat AS p8 ON p8.ItemClientRef = t.ItemClientRef AND p8.PickType = t.ClientUDF8
LEFT JOIN	tblItemPickFormat AS p9 ON p9.ItemClientRef = t.ItemClientRef AND p9.PickType = t.ClientUDF9
LEFT JOIN	tblItemPickFormat AS p10 ON p10.ItemClientRef = t.ItemClientRef AND p10.PickType = t.ClientUDF10

Peter Larsson
Helsingborg, Sweden

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-01-03 : 03:45:17

thanks...

i dont go for left join becoz it will produce be 3 rows for 1 row.. i want to select more columns, not rows... sigh dunno how to put into words what is exactly my situation :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 04:42:46

That is what the DISTINCT is for.
If you look at the execution plan, you will see that this query will perform much better.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources