CODEWITHSUNDEEP
t-sqlsql-server-2005conditional-statements
Zain Ali
Zain Ali

Reputation: 15983

SQL conditional AND clause?

In my query I want to add the and clause in query if a specific condition occur. See the where clause of my query

1     and ps.dept=dept.deptid  

2     and ps.sdept=deptsub.deptid 

3     and

4   if(ps.dept<>ps.sdept) 

5    begin

6    deptsub.Parent=dept.deptid 

7  end

8  and ps.deptcategory=deptcat.category

At 4 I want if condition fulfil then 6 should be added in query else not how is this possible.thanks!

Upvotes: 2

Views: 984

Answers (3)

ysth
ysth

Reputation: 98398

How about:

and ps.dept=dept.deptid  
and ps.sdept=deptsub.deptid 
and ( ps.dept=ps.sdept or deptsub.Parent=dept.deptid )
and ps.deptcategory=deptcat.category

Upvotes: 4

Andy White
Andy White

Reputation: 88385

and ps.dept=dept.deptid  
and ps.sdept=deptsub.deptid 
and
(
    ((ps.dept<>ps.sdept) and (deptsub.Parent=dept.deptid))
    or
    (ps.dept = ps.sdept)
)
and ps.deptcategory=deptcat.category

Upvotes: 3

jlnorsworthy
jlnorsworthy

Reputation: 3974

Try replacing lines 4, 5, 6, and 7 with this:

deptsub.Parent = 
case when ps.dept <> ps.sdept then dept.deptid 
else deptsub.Parent 
end

The case statement will substitute dept.deptid when your condition is met, otherwise it will just substitute deptsub.parent - which will always = deptsub.parent

Upvotes: 3

Related Questions