Today's question comes from David B.:
I think this might be a tough one. Basicly i do a join that result in a recordset. In one of the columns, i sometimes get a forign key, sometimes a NULL. What i want to do is to output some text+the forign key if there is a foreign key and nothing/NULL/some text if not so. One might think that
IsNullis the answer but it's not since
IsNull('sometext' + foreign_key, 'no fk')will never evaluate to NULL.
In access97, there is this command call
IIF(Immediate if) (
IIF(expr, if_so, else)) that would let me do the trick (
IIF(FK IS NULL, 'no fk', 'text' + fk)) but I can't find a corresponding function in SQL-server 6.5 or 7, that I use.
I'd really appriciate some advice!
David, here are a couple of solutions to this problem... (no
in SQL 7.5!)
1) Use a CASE expression:
SELECT foo, bar, CASE WHEN fk_col IS NULL THEN 'It is null!' ELSE 'the fk value is: ' + fk_col END FROM yadda
2) that fact that you mention "
'sometext' + foreign_key will never evaluate
to null" leads me to believe that you're using SQL 6.5. In SQL 7.0, the
opposite is true...
'text'+NULL yields null, unless you use the
CONCAT_NULL_YIELDS_NULL OFF command. If you start using SQL 7.0, you can
use the (much simpler)
There are probably other ways to do it (using some of the other NULL functions), but I think these would end up being the most readable.