CODEWITHSUNDEEP
sql
Rohan
Rohan

Reputation: 2030

where clause based on conditions

I have a query where I match a column. The value to be matched may be null or not null (some value exists). The problem arises when the matching takes place. In case value is present the matching like

table.column = somevalue

works fine, but in case the value is null, then the matching needs to be done as 

table.column is null

Is there some way that I can choose the condition being used in the WHERE clause based on the value?

Thanks in advance

Upvotes: 4

Views: 4969

Answers (6)

plang
plang

Reputation: 5656

What about ORing your WHERE clause?

SELECT
*
FROM table
WHERE ((table.column = 123) AND table.column IS NOT NULL))
OR    (table.column IS NULL)

Upvotes: 3

Rohan
Rohan

Reputation: 2030

I used decode and case statement to solve this problem This has been done in oracle. 


    AND DECODE(TSBI.SHIP_TRAIL_STATUS, 
               'L', 
               'TRUE', 
               'C', 
               (SELECT CASE 
                    WHEN TSBI.UPD_ON + 10 >= SYSDATE THEN 
                         'TRUE' 
                     ELSE 
                          'FALSE' 
                      END 
                FROM DUAL)) = 'TRUE'

In this condition I have checked the SHIP_TRAIL_STATUS. In case it returns 'L' the decode function returns TRUE. In case it returns 'C' the decode checks the UPD_ON value. Accordingly this CASE statement return TRUE or FALSE.

Upvotes: 1

faester
faester

Reputation: 15086

In ms sql you could do

      declare @match int
      select * from tbl where coalesce(tblValue, @match) = @match

That way you will compare @match with itself when tblValue is null.

Of course you can eliminate the parameter if it is null by switching the matching

      select * from tbl where tblValue = coalesce(@match, tblValue)

but the latter query may cause the query optimizer to neglect indexes on tblValue, so you should check the execution plan.

Upvotes: 1

Mike T
Mike T

Reputation: 43722

Use a CASE statement, e.g.:

SELECT
  CASE
    WHEN mycol IS NULL
      THEN 'it is null'
    WHEN mycol = 'foo'
      THEN 'it is foo'
    ELSE 'it is something else'
  END AS col
FROM mytable;

(Note: I don't know what DB you are using, so "IS NULL", "ISNULL", "ISNULL()" might be needed above)

Upvotes: 1

Aziz Shaikh
Aziz Shaikh

Reputation: 16544

Try using ISNULL(somevalue, 0). Provided that your "somevalue" table.column does not have 0 is a valid value. Instead of 0 you may use any string/number that will never appear as a valid value in table.column

Upvotes: 1

Balanivash
Balanivash

Reputation: 6867

Check the MySQL Control Flow functions

Upvotes: 1

Related Questions