CODEWITHSUNDEEP
db2
V.Hunon
V.Hunon

Reputation: 320

IBM DB2 Case in Where clause?

I have a SQL Statement where I have to check on conditions on rows since it has duplicates. Condition1 is to watch if the value from a column in tb1 is between two values in a two columns of tb2 but only on rows which has a 'Y' inside.

I have something like this:

SELECT DISTINCT

tb1.columnA, tb1.columnB, tb2.columnA, tb2.columnB,

CASE 
    WHEN tb1.col1 = 'Y'
    THEN CONCAT(tb1.col2,tb2.col2)
    ELSE 'no changes'
END as conditionColumn

FROM
    tb1 left outer join tb2 on tb1.columnX  tb2.columnX

WHERE   

CASE 
    WHEN tb1.col1 = 'Y'
    THEN Condition1
    ELSE Condition2
END

From what I saw before, a case statement is not allowed in the where clause? How can I handle this then?

EDIT

When I have a 'Y' in tb1.col1 the where clause should output me an ID specified in tb1.columnA and only the rows which values from tb1.ColumnB is between tb2.columnA and tb2.columnB. If it doesn't have a Y inside then it should simply just give me the ID

so I used this, but it gives me an syntax error:

SELECT DISTINCT

tb1.columnA, tb1.columnB, tb2.columnA, tb2.columnB,

CASE 
    WHEN tb1.col1 = 'Y'
    THEN CONCAT(tb1.col2,tb2.col2)
    ELSE 'no changes'
END as conditionColumn

FROM
    tb1 left outer join tb2 on tb1.columnX  tb2.columnX
WHERE   

CASE 
    WHEN tb1.col1 = 'Y'
    THEN tb1.columnA = 'MyID'
    AND tb1.columnB BETWEEN tb2.columnA and tb2.columnB
    ELSE tb1.columnA = 'MyID'
END

Upvotes: 1

Views: 11268

Answers (2)

Paul Vernon
Paul Vernon

Reputation: 3901

If you want to do this

WHERE   

CASE 
    WHEN tb1.col1 = 'Y'
    THEN Condition1
    ELSE Condition2
END

then, likely what you are wanting is this

WHERE   

    ( tb1.col1 = 'Y'  AND Condition1 )
 OR ( tb1.col1 <> 'Y' AND Condition2 )

Upvotes: 3

Charles
Charles

Reputation: 23793

You seem to be confused about the purpose of a WHERE clause...

All a WHERE clause does is decide rather or not a given row is returned. It can't change the data being returned.

To change the data, you need a CASE in the columns selected...

something like so...

SELECT DISTINCT
CASE 
    WHEN tb1.col1 = 'Y'
         AND tb1.columnB BETWEEN tb2.columnA and tb2.columnB
      then 'MyID'
    ELSE tb1.columnA
END as newColumnA,
    tb1.columnB, tb2.columnA, tb2.columnB,
CASE 
    WHEN tb1.col1 = 'Y'
    THEN CONCAT(tb1.col2,tb2.col2)
    ELSE 'no changes'
END as conditionColumn
FROM
    tb1 left outer join tb2 on tb1.columnX  tb2.columnX

Upvotes: 1

Related Questions