How to substitute for a non-existing column in a joined ta..

When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
In your case, you need to re-write the CASE statement to:
(CASE WHEN t2.t1pk IS NULL THEN 'N/A'
ELSE t2.name END) AS c2name

Another example that I have is:
DECLARE @Emp TABLE (EmpID int, Name varchar(50))
declare @EmpNickName TABLE (EmpID int, NickName varchar(50))

INSERT INTO @Emp VALUES (1,'John T. Thompson')
INSERT INTO @Emp VALUES (2,'Gina Jackson')
INSERT INTO @Emp VALUES (3,'Dan Smith')

INSERT INTO @EmpNickName VALUES (1,'John')
INSERT INTO @EmpNickName VALUES (2,'Gina')

SELECT e.EmpID, e.Name,
(CASE WHEN n.NickName IS NULL THEN 'N/A'
ELSE n.NickName
END) as NickName
FROM @Emp e
LEFT JOIN @EmpNickName n ON n.EmpID = e.EmpID



"Jeroen Mostert" wrote:

> On 2010-04-21 21:05, bob wrote:
> > Is there a simpler way than a UNION to return a default value of a joined
> > table for which a corresponding row does not exist?
> >
> > The following example (not a working one, of course) illustrates what I'm
> > after. I'd like to return 'N/A' as c2name if there is no matching row in t2
> >
> > SELECT t1.c1
> >
> If you put a comma here...
>
> > (CASE t2.t1pk
> > WHEN NULL THEN 'N/A'
> > ELSE t2.name END) AS c2name,
> >
> > FROM t1
> >
> > LEFT JOIN t2 ON t2.t1pk = t1.pk
> >
> ....then your query is syntactically valid and gives the desired result.
>
> If you need something more complicated, you'll have to be more specific.
>
> --
> J.
> .
>
 >> Stay informed about: How to substitute for a non-existing column in a joined ta.. 

Or just use the COALESCE function, e.g.,

SELECT t1.c1,
COALESCE( t2.t1pk, 'N/A') AS c2name,
FROM t1
LEFT JOIN t2 ON t2.t1pk = t1.pk

Tom

"bob" wrote in message

> Hi,
>
> Is there a simpler way than a UNION to return a default value of a joined
> table for which a corresponding row does not exist?
>
> The following example (not a working one, of course) illustrates what I'm
> after. I'd like to return 'N/A' as c2name if there is no matching row in
> t2
>
> SELECT t1.c1
>
> (CASE t2.t1pk
> WHEN NULL THEN 'N/A'
> ELSE t2.name END) AS c2name,
>
> FROM t1
>
> LEFT JOIN t2 ON t2.t1pk = t1.pk
>
> Thanks.
>
 >> Stay informed about: How to substitute for a non-existing column in a joined ta.. 

On 2010-04-21 22:52, Q wrote:
> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.

Wow, I don't know how I managed to miss that.

--
J.
 >> Stay informed about: How to substitute for a non-existing column in a joined ta.. 

"Jeroen Mostert" wrote in message

> On 2010-04-21 22:52, Q wrote:
>> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
>
> Wow, I don't know how I managed to miss that.
>
> --
> J.

Are you sure that 'IS NULL' works with CASE/WHEN? I know that it does with
WHERE, etc. but with CASE it seems to work without 'IS' only.
So, maybe you did not miss anything afterall
 >> Stay informed about: How to substitute for a non-existing column in a joined ta.. 

On 2010-04-22 18:32, bob wrote:
> "Jeroen Mostert" wrote in message
>
>> On 2010-04-21 22:52, Q wrote:
>>> When comparing to NULL value, you have to use IS NULL or IS NOT NULL.
>>
>> Wow, I don't know how I managed to miss that.
>>
> Are you sure that 'IS NULL' works with CASE/WHEN? I know that it does with
> WHERE, etc. but with CASE it seems to work without 'IS' only.
> So, maybe you did not miss anything afterall
>
>
CASE has two forms, the first for matching a single expression against
multiple others, like so:

CASE x
WHEN A THEN x_A
WHEN B THEN x_B
...
END

This form definitely does not work for NULL, or rather, the WHEN clauses
behave as if you had written an equality comparison out in full. You can
easily test this yourself: setting ANSI_NULLS will affect the result just
like in (in)equality comparisons (if ANSI_NULLS is OFF, A = NULL will be
true, the intuitive but wrong result).

The other form evaluates multiple boolean expressions, like so:

CASE
WHEN x = A THEN x_A
WHEN x = B THEN x_B
...
END

The expressions obey the regular rules and thus require IS NULL if you want
to test for NULL. In fact, this is the only form you can use if you want to
test for NULL in a CASE.

--
J.
 >> Stay informed about: How to substitute for a non-existing column in a joined ta..