To read the article online, visit http://www.4GuysFromRolla.com/webtech/sqlguru/q120999-1.shtml

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


Article Information
Article Title: SQL Guru: Checking NULL Values
Article Author: Scott Mitchell
Article URL: http://www.4GuysFromRolla.com/webtech/sqlguru/q120999-1.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers