problem in expression of a check constraint

Hi all,

I wrote the expression below in a table's check constraint

(CustomerTypeID = 0 and ContentSiteID is null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null ) or

( CustomerTypeID = 1 and ContentSiteID is not null and ResellerID is null and AffiliateID is null and WhiteLabeLID is null )

It saved it ok, but when I re-opened and viewed the expression again it was converted to:

([CustomerTypeID] = 0 and [ContentSiteID] is null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null

[CustomerTypeID] = 1 and [ContentSiteID] is not null and [ResellerID] is null and [AffiliateID] is null and [WhiteLabeLID] is null )

This is a different logic because the OR is not prioritised above the AND.

Any solution or should I move my condition to the table's Update and Insert triggers?

Thanks!

Ofer

You are safe because of the precedence of the operators. A reading from the Books On-Line:

When more than one logical operator is used in a statement, NOT is evaluated first, then AND, and finally OR. Arithmetic (and bitwise) operators are handled before logical operators.

If you swap the Ands and the OR in your expression, you will see that the evaluation order is preserved with parens.

Here is a quick sample with the results shown.

drop table tester

go

create table tester(

one int null,

two int null,

three varchar(10) null

)

go

alter table tester

Add Constraint AndOrAnd Check ( (one = 1 and three is null ) or (two = 0 and three is not null ) )

alter table tester

Add Constraint OrAndOr Check ( (one = 1 or three is null ) and (two = 0 or three is not null ) )

exec sp_helptext AndOrAnd

-->([one] = 1 and [three] is null or [two] = 0 and [three] is not null)

exec sp_helptext OrAndOr

-->(([one] = 1 or [three] is null) and ([two] = 0 or [three] is not null))|||

Thanks for your reply but it doesn't answer this problem.

I need the check constraint to have a codition of ( (x=1 and y is null) or (x=2 and y is not null) )

Replacing the or-s with and-s does a totally different check.

The SQL Server just drops the inner paranthesis, again changing the condition.

Is there a way to check my condition ?

Thanks.

|||

Sorry that my response was not clear. Your expression is being evaluated in the way you desire. It happens because any of the ANDs are being evaluated first. The server is removing the parenthesis precisely because they are redundant to the evaluation.

The example of swapping the conditions was only to show that the server will preserve the parenthesis if they are contrary to normal order of evaluation.

sql

Problem in checking null record

Hello,

I am using sql server 2000 as my backend.

If there is any null record in my table , I am getting following error:

***********

Conversion from type 'DBNull' to type 'String' is not valid.

**************

to overcome this error I want to check if its null then don't do anything else do something.

I have tried like this:

If rs.fields("ph").value="NULL" then

else

.......

....

...

end if

But this does not solve my problem.

Please help me i anyone can.

Thanks.

If rs.fields("ph") Is DbNull.Value Then
'it's null
Else
'it can be converted to a string
End If

Your solution test to see if the field = "NULL". Because you have NULL in quotes, you are actually testing to see if the field holds a string wiht the letters N U L L in it. The other thing about NULL is that nothing can equal null. Null means "unknown value", so it is impossible to compare one unknown value with another, because, well, they are unknown... The test for null is IsNull.

Problem handling NULL values in SQL Server 2000

Hi I have a function with if and else conditions. For some reason the
second if condition does not seem to work. It is not able to check for
Null values.
I am new to SQL programming and so I cant figure out the actual
problem.
Any help would be greatly appreciated. Cheers!
Here is the function.
ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
@.faSurname varchar(50) )
RETURNS varchar(50)
AS
BEGIN
DECLARE @.res varchar(50)
IF (@.moSurname = @.faSurname or @.moSurname = null)
BEGIN
SET @.res = 'The '+@.faSurname+' Family'
END
ELSE
IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
condition does not work */
BEGIN
SET @.res = 'The '+@.moSurname+' Family'
END
ELSE
IF (@.moSurname<> @.faSurname)
BEGIN
SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
END
RETURN @.res
ENDOn Aug 29, 9:57 am, Rex <rakesh...@.gmail.com> wrote:
> Hi I have a function with if and else conditions. For some reason the
> second if condition does not seem to work. It is not able to check for
> Null values.
> I am new to SQL programming and so I cant figure out the actual
> problem.
> Any help would be greatly appreciated. Cheers!
> Here is the function.
> ALTER FUNCTION dbo.labelSalutation ( @.moSurname varchar(50),
> @.faSurname varchar(50) )
> RETURNS varchar(50)
> AS
> BEGIN
> DECLARE @.res varchar(50)
> IF (@.moSurname = @.faSurname or @.moSurname = null)
> BEGIN
> SET @.res = 'The '+@.faSurname+' Family'
> END
> ELSE
> IF (@.faSurname = NULL and moSurname <> Null) /* <-- This
> condition does not work */
> BEGIN
> SET @.res = 'The '+@.moSurname+' Family'
> END
> ELSE
> IF (@.moSurname<> @.faSurname)
> BEGIN
> SET @.res = 'The '+@.moSurname+' & '+@.faSurname+' Family'
> END
> RETURN @.res
> END
Replace IF (@.faSurname = NULL and moSurname <> Null) with IF
(@.faSurname is NULL and moSurname is not Null)

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_IDint NOT NULL,
CUST_IDint NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_IDint NOT NULL,
ITEM_NAMEvarchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_IDint NOT NULL,
CUST_NAMEvarchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil
> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegr oups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>
|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.

Problem creating view

Hi
I have the following set up:
CREATE TABLE PURCHASE
(
ITEM_ID int NOT NULL,
CUST_ID int NOT NULL,
)
go
CREATE TABLE ITEM
(
ITEM_ID int NOT NULL,
ITEM_NAME varchar(32) NOT NULL
)
go
CREATE TABLE CUST
(
CUST_ID int NOT NULL,
CUST_NAME varchar(32) NOT NULL
)
go
CREATE VIEW PURCHASE_VIEW
AS
SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
FROM PURCHASE P, ITEM I, CUST C
WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
go
The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
for which a corresponding
ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
view to return a row for each row of the PURCHASE table, but with the
ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
in ITEM or CUST.
Is there some way I can achieve this?
Thanks,
Neil> However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
You can use an outer join will return purchases even without corresponding
items and customers. I must ask how how a purchase was possible for an
non-existent item or customer. Perhaps the foreign keys are missing,
leading to data integrity problems.
CREATE VIEW PURCHASE_VIEW
AS
SELECT
P.ITEM_ID,
I.ITEM_NAME,
P.CUST_ID,
C.CUST_NAME
FROM PURCHASE P
LEFT OUTER JOIN ITEM I ON
P.ITEM_ID = I.ITEM_ID
LEFT OUTER JOIN CUST C ON
P.CUST_ID = C.CUST_ID
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"neilsolent" <neil@.solenttechnology.co.uk> wrote in message
news:1171809173.950612.119490@.l53g2000cwa.googlegroups.com...
> Hi
> I have the following set up:
> CREATE TABLE PURCHASE
> (
> ITEM_ID int NOT NULL,
> CUST_ID int NOT NULL,
> )
> go
> CREATE TABLE ITEM
> (
> ITEM_ID int NOT NULL,
> ITEM_NAME varchar(32) NOT NULL
> )
> go
> CREATE TABLE CUST
> (
> CUST_ID int NOT NULL,
> CUST_NAME varchar(32) NOT NULL
> )
> go
> CREATE VIEW PURCHASE_VIEW
> AS
> SELECT P.ITEM_ID, I.ITEM_NAME, P.CUST_ID, C.CUST_NAME
> FROM PURCHASE P, ITEM I, CUST C
> WHERE (P.ITEM_ID = I.ITEM_ID) AND (P.CUST_ID = C.CUST_ID)
> go
> The problem I have is, the PURCHASE_VIEW only shows rows in PURCHASE
> for which a corresponding
> ITEM_NAME and CUST_NAME exist in ITEM and CUST. However, I want the
> view to return a row for each row of the PURCHASE table, but with the
> ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> in ITEM or CUST.
> Is there some way I can achieve this?
> Thanks,
> Neil
>|||On 18 Feb, 15:02, "Dan Guzman" <guzma...@.nospam-online.sbcglobal.net>
wrote:
> > However, I want the
> > view to return a row for each row of the PURCHASE table, but with the
> > ITEM_NAME and CUST_NAME set to NULL if no corresponding entry exists
> > in ITEM or CUST.
> You can use an outer join will return purchases even without corresponding
> items and customers. I must ask how how a purchase was possible for an
> non-existent item or customer. Perhaps the foreign keys are missing,
> leading to data integrity problems.
Many thanks, Dan. I will give this a try.
To answer your question, this is a contrived example, which I
simplified for the purpose of this post.