SQL Server : IF condition within where clause

Question

I am trying to put an if condition within a where clause and I cannot figure out the correct syntax for what I want.

The logic should be if a certain period is equal to a specific number then check to make sure pdftype is not null else then don't check it all.

Here's what I have so far:

where  
    g.ReportInstanceID = blah
    and rcr.FormID = blah
    and rcr.FormSectionID = blah
    and rcr.SubSectionID = blah
    and CASE rcr.DataCollectionPeriodID 
           WHEN 163 THEN (PDFType IS NOT NULL)
        END

I would use multiple conditions with OR as shown in answer below, but you can use CASE in WHERE: AND CASE WHEN rcr.DataCollectionPeriodID = 163 THEN PDFType ELSE 'X' END IS NOT NULL — Hart CO
– Hart CO, Commented Jan 30, 2017 at 18:31
This answer shows how a CASE expression can be used in a ON clause. The same works for a WHERE clause. — HABO
– HABO, Commented Jan 30, 2017 at 19:35

Jerrad · Accepted Answer · 2017-01-30 18:24:12Z

6

That's not how the CASE statement works. I think this does what you want:

where  g.ReportInstanceID = blah
    and rcr.FormID = blah
    and rcr.FormSectionID = blah
    and rcr.SubSectionID = blah
    and (
          (rcr.DataCollectionPeriodID = 163 and PDFType IS NOT NULL)
          or rcr.DataCollectionPeriodID <> 163
        )

answered Jan 30, 2017 at 18:24

Jerrad

5,3001 gold badge20 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gurwinder Singh · Accepted Answer · 2017-01-30 18:23:36Z

2

Try this:

. . .
AND CASE when rcr.DataCollectionPeriodID = 163 THEN
     case when PDFType is not null then 1 else 0 end
    else 1 END = 1

answered Jan 30, 2017 at 18:23

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

1 Comment

Sriram Over a year ago

hi the code works but can you help to explain how the else 1 and end = 1 works

Collectives™ on Stack Overflow

SQL Server : IF condition within where clause

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related