Now, I want to give the user a view of fruits with their respective colors. For this I want to give the user 3 options based on color:
1)show fruits with RED color
2)show fruits with Yellow color
3)show fruits with any color
I tried writing the following query:
from TABLE1 as A
TABLE2 as B
WHERE B.COLOR LIKE <variable>
my <variable> is assigned the value 1)'Red' for the first scenario,'yellow' for the 2nd & '%' for the 3rd. But with '%' I only get the following output:
NULL values cannot be compared to strings, so when you test to see if NULL is LIKE '%' the answer is NULL!
, b.value As colour
FROM @table1 As a
JOIN @table2 As b
ON b.id = a.id
WHERE Coalesce(b.value, 'NO COLOUR MATCH HERE!') LIKE @variable
thanks a ton george, that solved it. Another query by the way regarding the Coalesce command, I understand that its a kind of compressed IF ELSE construct. The syntax for Coalesce is everywhere mentioned as :
Coalesce(expr1,expr2,expr3..n) & is mentioned that it can take any number of arguments. But its failing for a single argument. Are 2 arguments the minimum??
Well I thought my query was a pretty generic SQL syntactical one so posted it here.
Nevertheless , maybe DB2 isnt allowing the single argument Coalesce function (works fine for multiple arguments in DB2).