CASE problem (or is it a null problem?)

I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor CompanyFirst, you should be using IS NULL. Second, you should have an ELSE in each
of those CASE's. Otherwise, the default is NULL. However, this can be done
without CASE's. Looks like you may want:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT
ISNULL (TIN, 'Dummy')
, ISNULL (PSTATE, 'Dummy')
, ISNULL (PCITY, 'Dummy')
, PNAME1
, PADDR
FROM dbo.EXPTRANS
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
I have this insert statement:
INSERT INTO dbo.TinNormalized
(TIN, PState, PCity, PName1, PAddr)
SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
Case When PSTATE = NULL Then 'Dummy' END,
Case When PCITY = Null Then 'Dummy' END,
PNAME1, PADDR
FROM dbo.EXPTRANS
it fails with this message:
Cannot insert the value NULL into column 'TIN', table
'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
The statement has been terminated.
I have tried it with IS NULL as well and yet I get the same error. What am I
missing?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company|||More correct is
Case WHEN TIN IS NULL THEN 'Dummy' ELSE TIN END
Bojidar Alexandrov|||Thank you all
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Andrew Madsen" <andrew.madsen@.harley-davidson.com> wrote in message
news:OpRcUtuLEHA.628@.TK2MSFTNGP11.phx.gbl...
> I have this insert statement:
> INSERT INTO dbo.TinNormalized
> (TIN, PState, PCity, PName1, PAddr)
> SELECT DISTINCT Case WHEN TIN = NULL THEN 'Dummy' END,
> Case When PSTATE = NULL Then 'Dummy' END,
> Case When PCITY = Null Then 'Dummy' END,
> PNAME1, PADDR
> FROM dbo.EXPTRANS
> it fails with this message:
> Cannot insert the value NULL into column 'TIN', table
> 'CNATEST.dbo.TinNormalized'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> I have tried it with IS NULL as well and yet I get the same error. What am
I
> missing?
> --
> Andrew C. Madsen
> Information Architect
> Harley-Davidson Motor Company
>