T-SQL Case Statement - Invalid syntax 2

[1DMF]

Hi,

I'm having trouble getting my head round how you use a CASE statement to provide conditions for a WHERE clause.

i have this...

WHERE (Ins_Lnk Is Null) AND 

			CASE @SType
				WHEN 'Paid' THEN "(Status <> 'NPW') AND "  
				WHEN 'UnPaid' THEN "(Status <> 'NPW') AND "
				WHEN 'ClawBack' THEN "(Clawback_Date IS NOT NULL) AND " 
		    END

			CASE @LDate
				WHEN 'NULL' THEN "(Lender_Date IS NULL) AND "
				WHEN 'NOT NULL' THEN "(Lender_Date IS NOT NULL) AND "
			END

			(Adv_MemNo <> 'TEST_ACC')
	ORDER BY CompanyName ASC

I have now read that

It's important to understand that in SQL, CASE is an expression and not an executable "control-of-flow" statement.

So I'm totaly lost over how I convert the above logic into a valid CASE statement.

Perhaps I shouldn't be using CASE ?

All help understanding how I add logic and selectable parts of a where statements based on the value of input parameters is appreciated.

cheers,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[bborissov]

WHERE (Ins_Lnk Is Null)    AND
      ((@SType = 'Paid'     AND (Status <> 'NPW')) OR
       (@SType = 'UnPaid'   AND (Status <> 'NPW')) OR
       (@SType = 'ClawBack' AND (Clawback_Date IS NOT NULL))) AND

      ((@LDate = 'NULL'     AND Lender_Date IS NULL) OR
       (@LDate = 'NOT NULL' AND Lender_Date IS NOT NULL))     AND

      (Adv_MemNo <> 'TEST_ACC')

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[MarkSweetland]

You can use CASE as a boolean to test numerous conditions in the WHERE clause. Watch the use of parentheses to group logic blocks accordingly as well as the AND/OR operators:

where (
		1 = (CASE when @SType = 'Paid' and (Status <> 'NPW') then 1 else 0 end)
		or 
		1 = (CASE when @SType = 'UnPaid' and (Status <> 'NPW') then 1 else 0 end)
		or 
		1= (CASE when @SType = 'Clawback' and (Clawback_Date IS NOT NULL) then 1 else 0 end)
	  )	
		and
	  (
		1 = (CASE when @LDate is NULL and (Lender_Date IS NULL) then 1 else 0 end)
		or 
		1 = (CASE when @LDate is NOT NULL and (Lender_Date IS NOT NULL) then 1 else 0 end)
	  )
)

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[1DMF]

Thanks Borislav but that's not what i'm after.

I don't want 'OR' I only want certain records 'IF' a parameter is a certain value.

This way I can use the same SP for different search criteria.

Mark -> I'm not sure I understand

why would I want when 1=0 or 1=1 or 1=0? when @SType = 'UnPaid'?

If you have where 1=1 you get every record in the table don't you?

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[1DMF]

Well mark, you are right, I haven't tried your code yet, but I did some more investigating and found this..

WHERE 1 = (CASE 
            WHEN @IdType='Student' AND StudentId = @Id 
                  THEN 1
            WHEN @IdType='Teacher' AND TeacherId = @Id 
                  THEN 1
            WHEN @IdType='Group' AND GroupId = @Id 
                  THEN 1 
            ELSE 0 END)

reading it as normal SQL doesn't make sense, but i then interpreted it with your guidance above and tried it with another SP...

WHERE 1 = (CASE 
            WHEN @PCode <> '' AND Replace(Postcode,' ','') Like @PCode + '%' THEN 1
            WHEN @PCode = '' AND ((DOB1 >= @DOB1 AND DOB1 <= @DOB2) OR (DOB2 >= @DOB1 AND DOB2 <= @DOB2)) THEN 1
            ELSE 0 END)
	AND Rec_Type = @RType

And it does exactly what I want, so now I get the logic,

Many thanks!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta

 

[MarkSweetland]

Exactly. Since the only way the CASE statement evaluates to 1 is for the CASE statement (using a column from each row) to compute if the row meets the criteria to resolve the boolean value to true and therefore = 1.

If you were to have only variables that do not change for each row in the CASE statement it would be true (1) for each row and return all rows (or false for all rows and return nothing)

Something like this:

WHERE 1 = case when @DOB1 < @DOB2 then 1 else 0 end

Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

 

[1DMF]

Thanks for your help, your suggestion wasn't what i was looking for but I got it working with...

WHERE (Ins_Lnk Is Null) AND 

        1 = (CASE 
				WHEN @SType = 'Paid' AND (Status <> 'NPW') AND (Lender_Date IS NOT NULL) THEN 1 
				WHEN @SType = 'UnPaid' AND (Status <> 'NPW') AND (Lender_Date IS NULL) THEN 1
				WHEN @SType = 'Clawback' AND (Clawback_Date IS NOT NULL) THEN 1
				ELSE 0
			END)

		 AND (Adv_MemNo <> 'Test_Acc')

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

MIME::Lite TLS Email Encryption - Perl v0.02 beta