Wednesday, July 29, 2015

CASE statement in WHERE clause of SQL


I'd situation where I had to search with either Item_ID or Item_Title. That is, if Item_ID is blank then only search with Item_Title, otherwise search with Item_ID only.

So to handle the situation CASE statement was used in WHERE clause of the SQL.

SELECT ITEM_DESCR
FROM ITEM_TBL
WHERE ITEM_TYPE=:1
    AND ( CASE WHEN (ITEM_ID=' ' OR ITEM_ID='' )
                 AND ITEM_TITLE =:2 THEN 1 WHEN (ITEM_ID<>' ' OR ITEM_ID<>'')
                 AND ITEM_ID = :3 THEN 1 ELSE 0 END=1);

No comments:

Post a Comment