The SQL Guru Answers your Questions...
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 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,
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
