sql if

lazyQuestions

hi, i'm not too famialiar with sql. i use it quite a bit but i'm really only familiar with the basics so i'm looking for a little help.

I'm trying to pull back rows from multiple tables and i want to use an if caluse to check if a specific column = 1 then another specific column should have a nulll value...

so say " if al1.columnOne=1 then al2.columnTwo is null " kinda thing but dont know what way to go about it, what the syntax is etc

Any help would be appreciated, thanks.

Art_Wolf

I believe what you want is the CASE function.

So you would have

SELECT
   CASE columnA
      WHEN 1 THEN
           'text'
      WHEN NULL THEN
           0
   END
FROM
   tableX
WHERE
   columnB = 'Test'

lazyQuestions

Art_Wolf wrote: »

I believe what you want is the CASE function.

So you would have

SELECT
   CASE columnA
      WHEN 1 THEN
           'text'
      WHEN NULL THEN
           0
   END
FROM
   tableX
WHERE
   columnB = 'Test'

thanks Art for the quick reply.

I dont think that is suiting tho? i just want to exclude certain rows , so when colA=1 then colB should be = to null.

Art_Wolf

Ah sorry, now I understand!

Possibly the clearest way would be to split up the sql and union the results or you could just have it as simple as having an OR in your where clause:

WHERE
columnA <> 1
OR
columnA = 1
AND columnB IS NULL

Or if there is a lot of conditions and that makes it bloated, you could look at a nested decode:

WHERE 'A' = decode(columnA, 1, decode(columnb, null, 'A', 0), 'A')

amen

WHERE
columnA <> 1
OR
columnA = 1
AND columnB IS NULL[code]

that to me is not very clear as to what the original programmers intention was.

this would be better

[code]WHERE
{
    (columnA <> 1)
OR
  (columnA = 1 AND columnB IS NULL)
)

don't forget using ORs can impact on you query execution time