Hi, I have 2 tables table1 & table2::

TABLE1:
________________
INDEX....VALUE

1............Apple
2............Banana
3............Peaches
4............Orange
----------------

TABLE2:
________________
INDEX....COLOR

1...........Red
2...........Yellow
----------------

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:

select A.VALUE,B.COLOR
from TABLE1 as A
LEFT JOIN
TABLE2 as B
ON A.INDEX=B.INDEX
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:

________________
VALUE.....COLOR

Apple.......Red
Banana....Yellow
-----------------

While this is understandable why I am getting this output, can anybody help me in correcting the query to get the following o/p for the 3rd scenario:

_________________
VALUE.....COLOR

Apple..... Red
Banana....Yellow
Peaches
Orange
-----------------

would really appreciate any help,
thanks

change the keyword WHERE to the keyword AND

the condition on color belongs in the ON clause of the join, not the WHERE clause

NULL values cannot be compared to strings, so when you test to see if NULL is LIKE '%' the answer is NULL!

Are you sure about that one Rudy?

If I search for "RED" I do not want the NULLs returned.

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??

thanks.

Failing?

Coalesce can take oodles of parameters and returns the first non-null value

I removed the second parameter from Coalesce(...) and tried...

Is it confusing it with a user defined function??
I am using DB2 by the way

maybe DB2 doesn't support all ANSI SQL functions?

you did post in the ANSI SQL forum, you know

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).

of what use would a single argument COALESCE be?

if foo is not NULL, then COALESCE(foo) will return foo

if foo is NULL, then COALESCE(foo) will return NULL

why would you use COALESCE(foo) when foo by itself gives the same result?

SQL-2003 (ISO/IEC 9075-2:2003 (E)):
6.11 <case expression>
[...]
<case abbreviation> ::=
NULLIF <left paren> <value expression> <comma> <value expression> <right paren>
| COALESCE <left paren> <value expression> { <comma> <value expression> }... <right paren>
[...]

I.e. two arguments or more.