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 IsNull
is the answer but it's not sinceIsNull('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,
David, here are a couple of solutions to this problem... (no IIF()
, perhaps
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 SET
CONCAT_NULL_YIELDS_NULL OFF
command. If you start using SQL 7.0, you can
use the (much simpler) ISNULL('text'+fk,'no fk')
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.
Sean
Read Other SQL Guru Questions |