Condition in Where clause query

Question

Condition in Where clause query

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

pwalter83 (3/9/2011)
Thanks a lot for your answers but would it be possible to convert your solution in a way that they can be included in the WHERE clause section ?

Why?

I picked a temp table and a join to avoid having to process the split and distinct more than once.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pwalter83 SSChampion Points: 14571 More actions · Answer 2

David Burrows (3/9/2011)
Sorry my bad the inner CASE is missing END, try this
AND CASE WHEN MG_BOOKING.TRADE_CD IS NULL THEN
CASE WHEN BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1 ELSE 0 END
WHEN BA.TRADE IS NULL THEN 0
WHEN MG_BOOKING.TRADE IN (select distinct Item From dbo.Split(@trades,','))
AND BA.TRADE IN (select distinct Item From dbo.Split(@trades,',')) THEN 1
ELSE 0
END = 1

Hi David,

Thanks a lot for your reply.Your solution worked wonders !!! However, can you please tell what do 0 and 1 denote in this context ? I know its a stupid question but I am very new to SQL and still on a learning curve.

Thanks and Regards,

Paul

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

Nothing specific. You could replace all the 1s with 'a' and the 0s with 'b' and get the same result. It's just a trick for evaluations case conditions.

What he's doing is setting the results of the case expression to either 0 or 1 based on the logic within, then comparing that with 1 to get the results he wants.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

pwalter83 SSChampion Points: 14571 More actions · Answer 4

GilaMonster (3/10/2011)
Nothing specific. You could replace all the 1s with 'a' and the 0s with 'b' and get the same result. It's just a trick for evaluations case conditions.
What he's doing is setting the results of the case expression to either 0 or 1 based on the logic within, then comparing that with 1 to get the results he wants.

Thanks a lot for your explanation, Gail. I was wondering can the same result be achieved without using any 1 and 0 (or for that matter a and b). Or is it mandatory to use them ?

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

Why?

There probably are, but they'll be hectically complex, hard to read, likely bad performing and hard to maintain. David wrote it that way for a reason. It's a reasonably common pattern.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass