AND operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

AND operator

Author

Topic

dthornton
Starting Member

2 Posts

Posted - 2011-06-21 : 16:28:05

The following SQL returns 2 when both conditions evaluate to TRUE. What's going on...

-- my table
CREATE TABLE [dbo].[Order_Label](
[OrderID] [int] NOT NULL,
[Version] [int] NOT NULL,
[ExternalID] [varchar](100) NULL
)

-- my data
INSERT INTO Order_Label(OrderID, Version, ExternalID) values(1,1,null)
INSERT INTO Order_Label(OrderID, Version, ExternalID) values(1,2,null)
INSERT INTO Order_Label(OrderID, Version, ExternalID) values(1,3,'A')
INSERT INTO Order_Label(OrderID, Version, ExternalID) values(1,4,'B')

-- the code
declare @ExternalMedicationID varchar(100)
set @ExternalMedicationID = 'C'
if
( -- ExternalID does not exist in this Order
@ExternalMedicationID NOT IN
(SELECT OL1.ExternalID
FROM Order_Label OL1
WHERE OL1.OrderID = 1)
)
AND
(
--Is there an entry for this Order without any ExternalID
(SELECT OL2.ExternalID
FROM Order_Label OL2
WHERE OL2.OrderID = 1
AND OL2.Version = (SELECT MAX(OL3.Version)
FROM Order_Label OL3
WHERE OL3.OrderID = 1)
) IS NOT NULL
)
begin
select 1 -- should insert new
end
else
begin
select 2
end

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 16:38:10

execute just this part

declare @ExternalMedicationID varchar(100)
set @ExternalMedicationID = 'C'
if
( -- ExternalID does not exist in this Order
@ExternalMedicationID NOT IN
(SELECT OL1.ExternalID
FROM Order_Label OL1
WHERE OL1.OrderID = 1)
)

select 1
else
select 2

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2011-06-21 : 16:39:14

so u could add this

and ExternalID is not null

dthornton
Starting Member

2 Posts

Posted - 2011-06-21 : 16:59:18

Thanks, that does it. Interesting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources