2

I like to use "IF" condition in where clause. From various threads, I understand that one of the options is CASE expression but I couldn't figure it out.

Sample Code :

select * from sampleTable
where 
  If @taxtype = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  End If

Any help is greatly appreciated.

Thank you!

Brad
  • 11,934
  • 4
  • 45
  • 73
Jan
  • 25
  • 2

2 Answers2

6
select * from sampleTable
where 
  case when @taxtype = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  end

Looks like this'll work with Postres

Edit:

Leaving my original answer because the gist works but Postgres doesn't have a concept of variables like other RDBMSs so I re-wrote this as

WITH myconstants as (SELECT 'P'::text as vtaxtype)

select * from sampleTable
where 
  case when (select vTaxType from myconstants) = 'P' then
    (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER')))
  Else
    (taxtype = 'E' and code not in ('MER','SER'))
  end;

Here's a SQL Fiddle showing that

Community
  • 1
  • 1
Brad
  • 11,934
  • 4
  • 45
  • 73
  • 1
    Seems this is not working: http://stackoverflow.com/questions/37815761/syntax-error-when-using-case-in-where-clause – UnDiUdin Jun 14 '16 at 15:11
3

It looks like you can do that by adding an OR

select * from sampleTable
where 
  (
    @taxtype = 'P' 
    AND
    (taxtype = 'P' or  (taxtype = 'E' and code  in ('MER','SER')))
  )
  OR
  (
    taxtype = 'E' 
    AND 
    code in ('MER','SER')
  )

An example of how to do it with case statements (for the sake of the question at hand). This works in SQL Server

select * from @sampleTable
where 
  case when @taxtype = 'P' then
    case when (taxtype = 'P' or  (taxtype = 'E' and code in ('MER','SER'))) then 1 else 0 end
  Else
    case when (taxtype = 'E' and code not in ('MER','SER')) then 1 else 0 end
  end =1
Rick Paul
  • 76
  • 6