Geek Answers Handbook

Where the condition is based on conditions

I have a query where I map a column. The value to be matched can be null or non-null (some value exists). The problem occurs when a match occurs. In the case where a value is present, a matching like

table.column = somevalue

works fine, but if the value is null, the match should be done as

 table.column is null

Is there a way I can select a condition that is used in a WHERE clause based on a value?

Thank you in advance

+4
source share
6 answers

How about ORing your WHERE ?

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

Use the CASE statement, for example:

 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 do not know which database you are using, so "IS NULL", "ISNULL", "ISNULL ()" may be required above)

+1
source

Check MySQL Control Flow Functions

+1
source

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

+1
source

In ms sql you could do

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

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

Of course, you can exclude the parameter if it is zero by switching the corresponding

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

but the last query can cause the query optimizer to neglect the indices on tblValue, so you should check the execution plan.

+1
source

I used decoding and case register to solve this problem. This was 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 state, I checked SHIP_TRAIL_STATUS. In case it returns 'L', the decoding function returns TRUE. In case it returns "C", decoding checks the value of UPD_ON. Accordingly, this CASE statement returns TRUE or FALSE.

+1
source

More articles:


All Articles