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
>

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 Company
First, 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
>
sql

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
>

cascading parameters

Hi,

I have a listbox which selects distinct brands from the products table.

Then another list box which lists all the orders with order description. Each order has a unique system generated ORDERID and the user provides the orderdescription which could be duplicate.

Depending on the Brand selected by the user in the earlier list box, only those orders containing the the brands in order details files should be available for selection in the list box.

e.g. BRands Lisbox shows: Cream A, Cream B and Cream C

If the user selects 'Cream A', then the next list box shows orderdescription as 'Cream A order for regular sale','Cream A order for exhinition sale', 'Cream A order for exhibition sale'

The problem here is that if the user selects a description which is duplicate (could be the case), then the system brings back the wrong order details.

How is it possible to allow a user to select a order description but search on the OrderID?

Thanks for the help

regards

josh

Hi,

this should work by simply defining two parameter queries whereas the orders query uses the parameter value of the brand parameter.

Regards, Alex

Cant Use Distinct here!

Hi All!!!
I'd Like to know how can I select one only row for each reference... I will post here the SQL clause
Note that I am working on Access

SELECT Badges.BadgeReference, Visitors.VisitorName, EventVisitors.VisitorCheckOutDate, EventVisitors.Event, EventVisitors.VisitorBadge, Visitors.VisitorState
FROM (Visitors INNER JOIN Badges ON Visitors.VisitorReference = Badges.BadgeReference) INNER JOIN EventVisitors ON (Visitors.Visitor_ID = EventVisitors.Visitor) AND (Badges.Badge_ID = EventVisitors.VisitorBadge)
WHERE (((Badges.BadgeReference) Is Not Null) AND ((EventVisitors.VisitorCheckOutDate) Is Not Null) AND ((Visitors.VisitorState)=0))
ORDER BY EventVisitors.VisitorCheckOutDate DESC;

It returns to me all the time the badge was out in each event... I want it to show the last event that each BadgeReference was retrieved...

Thanks in advance!SELECT Badges.BadgeReference
, Visitors.VisitorName
, EV.VisitorCheckOutDate
, EV.Event
, EV.VisitorBadge
, Visitors.VisitorState
FROM (
Visitors
INNER
JOIN Badges
ON Visitors.VisitorReference
= Badges.BadgeReference
)
INNER
JOIN EventVisitors as EV
ON Visitors.Visitor_ID = EV.Visitor
AND Badges.Badge_ID = EV.VisitorBadge
WHERE Badges.BadgeReference Is Not Null
AND EV.VisitorCheckOutDate
= ( select max(VisitorCheckOutDate)
from EventVisitors
where VisitorBadge = EV.VisitorBadge )
AND Visitors.VisitorState = 0
ORDER
BY EV.VisitorCheckOutDate DESC|||Thank you very very very very and a lots of many manys Much!!!!!

You have saved my day... You sure are pretty good at this ;)

See ya

Keep up that good work that you do :)