Null Columns

  • Thread starter Thread starter Dan M
  • Start date Start date
D

Dan M

I have a job positions table containing a field for last
name. There are cases when this field will be null (when
no one is assigned and the position is "open").

I'm attempting to use a SQL statement (in my adp file
pulling from SQL Server data) to display (in a listbox)
the last name of the person in the job position, or the
word "open" if there is no one. Here's my SQL for the
list box rowsource:

SELECT PositionID, JobPosition, CASE WHEN (LastName =
Null) THEN 'Open' ELSE LastName END as Name FROM
tblJobPositions

Usually, when I use the CASE WHEN syntax, I get the
desired output. But in this case, when the LastName
column is null, the output is null and my CASE WHEN logic
seems to be ignored.

I've experimented with other options to test, like
CASE WHEN (LastName = '') THEN 'Open' ELSE LastName END as
Name
and it works fine (assuming I have an empty string last
name in the table). It just has problems with NULL
values. Anyone know why?
 
That sounds great Joe, but I'm not using a WHERE clause.
I'm using the new T-SQL CASE WHEN. It has basically
replaced the old IIF that worked in older versions of
Access (and current version when dealing with an Access
database).

In an Access project (which I'm using), IIF can't be used
to conditionally output a column. CASE WHEN works pretty
well, but it doesn't accept the ISNULL function (or I
can't make it work). It will accept MyField = NULL, but
it won't replace the column's NULL value with my
conditional value.
 
Sorry Dan, I overlooked the CASE WHEN. I'm not to familiar with Access only
SQL server.
Hope you get some help from someone else.

Joe
 
Interesting. It appears that is exactly what's happening.

In attempting to fix this, I was trying to jam the ISNULL
function into the middle of the CASE WHEN syntax. Bad
approach.

And, for anyone reading this, the answer was much
simpler. To conditionally return an optional value from a
column when a NULL is encountered, here's all ya do:

SELECT ISNULL(LastName, 'Unlisted') FROM tblNames

In my example, if a row returns a last name, you'll see
it. If the last name is null, "Unlisted" would be
returned.
-----Original Message-----
The problem is probably the same as Dan's initial replay.
Click to expand...
In almost all DB systems, whether Access or Oracle or
MySQL or whatever, any expression that tests directly for
null will be evaluated as null. That is, WHERE foo=null or
CASE foo=null will all evaluate to null and hence be
interpreted as false. In all cases, you must use IsNull
(foo) to test a column for a null value. Whether the test
is part of a WHERE or a CASE or a HAVING or whatever is
not important. What is important is that you can't use a
condition that tests for foo=null, foo <> null, foo >
null, etc. All of these will be false not matter what
value foo hase.
 
Back
Top