if statements in sql query

Question

Good morning all. I have an issue with a query. I want to select something in a query only if another field is somethingelse. The below query will better explain

select  
Case isnull(rl.reason,'Not Found')
  When 'D' then 'Discontinued'
  When 'N' then 'Not Found'
  When 'I' then 'Inactive'
  When 'C' then 'No Cost'
  When '' then 'Not Found'
End as Reason, ***If statement to select pv.descriptor only if reason is in ('D','I','C')***pv.descriptor
from table1 as rl
left join  table2 as v on v.field= rl.field


***Here i want an if statment to run if reason is in ('D','I','C')***
left join table3 as pv on 
Case rl.scantype
 when 'S' then cast(ltrim(rtrim(pv.field#1)) as varchar)
 when 'U' then cast(ltrim(rtrim(pv.field#2)) as varchar)
 when 'V' then cast(ltrim(rtrim(pv.vfield#3)) as varchar)
end
= rl.scan and pv.vend_no = rl.vendnum
***'**If statement ends*****

 left join storemain..prmastp as p on p.emuserid = rl.userid
 where rl.scandate between GetDate() -7 and GetDate() order by rl.scandate desc

I want the if statement to select the descriptor only if the reason selected is a 'D','I',or'C'. If not I want a null value there because i will not do the join to get that variable unless the reason is a 'D','I','C'

BY the way, I can used a case statement where i used it in the middle of the left join. It works perfectly fine. That's not my issue.

Answer 1

What motivates the combination of two queries? Joining could cause a radically different evaluation plan to be required... Selecting between two plans based on the values of variables (or worse - columns!) is not something the query optimizer does well.

You will be far better off if you write two queries and use a SQL IF statement to flow control to one of them.

Edit: What should the query return if there are two rows from rl, one with reason=D and one with reason=S?

Answer 2

I'd keep it simple so whoever supports your code in the future can figure out how to maintain it (when they add another reason code, for example).

It may not be as performant, but it's more maintainable.

Also, is there a way you can get those codes in a table and test for a marker, instead of having them hard-coded in sql?

Answer 3

If you want it in one query, you HAVE to do the join. Using left joins and case statement as you have, you can ensure pv.descriptor is shown as null if that is what you want in certain cases.

If you want control flow, you will need to use T-SQL

If performance is your concern, you shouldn't be joining on computed values. Rethink the database design. You likey want to create new columns for your join, and may want to create intermediary tables if you have many-to-many relationships.

Answer 4

I think that you want to only join to pv if v.reason is (D, I, C) - is that right? If that's your problem, just change your JOIN clause to:

LEFT JOIN table3 as pv ON 
   LTRIM(RTRIM(
       CASE rl.scantype
          WHEN 'S' THEN pv.field#1
          WHEN 'U' THEN pv.field#2
          WHEN 'V' THEN pv.field#3
       END
   )) = rl.scan 
   AND rl.vendnum = pv.vend_no
   AND rl.reason IN ('D', 'I', 'C')

Of course, you also have "If statement to select pv.descriptor only if reason is in ('D','I','C') [as] pv.descriptor" in the SELECT clause. So, assuming you want that instead, try this:

SELECT
   /* your other columns */
   CASE 
      WHEN rl.reason IN ('D', 'I', 'C') THEN pv.descriptor
      ELSE NULL --optional, since it'll default to NULL
   END as descriptor