Help with constraint

Hi,
I have a table where I want a certain condition with 3 of
its fields. I want only 1 of them not to be null.
For example, if the fields are A,B,C these combinations are ok:
A B C
NULL 3432 NULL
NULL NULL 554
333 NULL NULL
Howere these are not:
A B C
NULL NULL NULL
NULL 5454 554
333 545 6858
I'm not very familiar with the syntax of constraints, and I did this:
check(
case when A is null then 0 else 1 end+
case when B is null then 0 else 1 end+
case when C is null then 0 else 1 end
=1)
but I get a syntax error.
Any ideas?
Thanks!You could do something like this.
check(
A IS NOT NULL
OR B IS NOT NULL
OR C IS NOT NULL)
"Star" wrote:

> Hi,
> I have a table where I want a certain condition with 3 of
> its fields. I want only 1 of them not to be null.
> For example, if the fields are A,B,C these combinations are ok:
> A B C
> NULL 3432 NULL
> NULL NULL 554
> 333 NULL NULL
> Howere these are not:
> A B C
> NULL NULL NULL
> NULL 5454 554
> 333 545 6858
> I'm not very familiar with the syntax of constraints, and I did this:
> check(
> case when A is null then 0 else 1 end+
> case when B is null then 0 else 1 end+
> case when C is null then 0 else 1 end
> =1)
> but I get a syntax error.
> Any ideas?
> Thanks!
>|||That doesn′t work but I appreciate your help.
Even with that condition I can have something like this:
A B C
NULL 5454 554
I want only one of them to be populated.
Patrik wrote:
> You could do something like this.
> check(
> A IS NOT NULL
> OR B IS NOT NULL
> OR C IS NOT NULL)
>
> "Star" wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||I think I got it to work.
Just in case someone is interested:
(case when ([A] is null) then 0 else 1 end + case when ([B] is null)
then 0 else 1 end + case when ([C] is null) then 0 else 1 end = 1)|||Ok, misunderstood you.
Try this
check(
(case isnull(a,-1) when -1 then 0 else 1 end + case isnull(b,-1) when -1
then 0 else 1 end + case isnull(c,-1) when -1 then 0 else 1 end) = 1
)
"Star" wrote:

> That doesn′t work but I appreciate your help.
> Even with that condition I can have something like this:
> A B C
> NULL 5454 554
> I want only one of them to be populated.
>
>
> Patrik wrote:
>|||drop table aa
go
create table aa(a int, b int, c int)
go
alter table aa add constraint chk_abc check(
(a is null and b is null and c is not null) or
(a is null and b is not null and c is null) or
(a is not null and b is null and c is null)
)
go
insert into aa values(1,null,null)
insert into aa values(null,1,null)
insert into aa values(null,null,1)
insert into aa values(1,1,null)
insert into aa values(null,1,1)
insert into aa values(1,null,1)
insert into aa values(1,1,1)
insert into aa values(null,null,null)
go
select * from aa|||On Thu, 26 Jan 2006 13:51:41 -0500, Star wrote:

>Hi,
>I have a table where I want a certain condition with 3 of
>its fields. I want only 1 of them not to be null.
>For example, if the fields are A,B,C these combinations are ok:
>A B C
>NULL 3432 NULL
>NULL NULL 554
>333 NULL NULL
>Howere these are not:
>A B C
>NULL NULL NULL
>NULL 5454 554
>333 545 6858
>I'm not very familiar with the syntax of constraints, and I did this:
>check(
>case when A is null then 0 else 1 end+
>case when B is null then 0 else 1 end+
>case when C is null then 0 else 1 end
>=1)
>but I get a syntax error.
>Any ideas?
Hi Star,
Though you've gotten some alternative formulations, I fail to see why
you would have gotten syntax errors. The code below runs fine for me:
CREATE TABLE Star(A int, B int, C int,
check(case when A is null then 0 else 1 end +
case when B is null then 0 else 1 end +
case when C is null then 0 else 1 end = 1)
)
go
-- Accepted
INSERT INTO Star (A, B, C)
select 1, null, null
union all
select null, 1, null
union all
select null, null, 1
-- Rejected
INSERT INTO Star (A, B, C)
SELECT 1, 1, null
INSERT INTO Star (A, B, C)
SELECT null, null, null
INSERT INTO Star (A, B, C)
SELECT 1, 1, 1
-- Show results
select * from Star
go
DROP TABLE Star
go
Hugo Kornelis, SQL Server MVP|||>From the samp[le data, it looks like they are all positive integers, so
we can use this trick:
CHECK ( COALESCE (SIGN(a), 0)
+ COALESCE (SIGN(b), 0)
+ COALESCE (SIGN(c), 0) = 1)
if you have negative numbers use SIGN (ABS(x)) and if you have zeroes,
use SIGN (ABS(x+1))|||usually problems like this arise when mutually exclusive subtypes are
stored in one table. Is that the case? Are you considering splitting up
the table?

help with arithmetic overflow error with insted of update trigger

Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
When I perform an update on certain records, i.e.:
UPDATE mytableview
SET field1 = 1
WHERE userid = 1234
I am finding that *some* user id's result in the follwing error:
"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."
mytable view has a number of data fileds. All the data fields are of type
smalldatatime.
When i compare the user record of a userid that causes an error to one that
doesnt cause an error, the dates do vary, where some date fields have NULL's
or correctly formated smalldatatime values (yes I know about the restriction
of smalldatetime to range between 1900 and 2079).
The odd thing is that even if i am updating a non-date field within the
view, the above arithmetic error still occurs.
My trigger looks like the following:
CREATE TRIGGER mytrigger ON mytableview
INSTEAD OF UPDATE
AS
DECLARE @.mydate datetime
SELECT @.mydate = GETDATE()
UPDATE mytable SET
field1 = ISNULL(inserted.field1, 0),
field2 = ISNULL(inserted.field2, 0),
field3 = ISNULL(inserted.field3, 0),
date1 = inserted.date1,
date2 = @.date+30
FROM inserted
WHERE mytable.userid = inserted.userid
Am i getting this error because i am mixing a date2 fiels (which is of type
smalldatetime) with @.date (which is of type datetime) ?
Any help most appreciated.Do you need the extra ms or time range? If not try:
DECLARE @.mydate smalldatetime
SELECT @.mydate = GETDATE()
SELECT @.mydate
HTH
Jerry
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||Also, drop the SELECT @.mydate -- was just for testing. Basically using the
SMALLDATETIME data type instead of DATETIME.
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
> Do you need the extra ms or time range? If not try:
> DECLARE @.mydate smalldatetime
> SELECT @.mydate = GETDATE()
> SELECT @.mydate
> HTH
> Jerry
> "PWalker" <pwalker@.nospam.com> wrote in message
> news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Sorry, I meant to say:
mytable view has a number of *date* fields. All the *date* fields are of
type
smalldatatime.
.. late night
cheers, peter
"PWalker" <pwalker@.nospam.com> wrote in message
news:uCQYjxz0FHA.3256@.TK2MSFTNGP09.phx.gbl...
> Hi, I have a view set up with an INSTEAD OF UPDATE trigger specified.
> When I perform an update on certain records, i.e.:
> UPDATE mytableview
> SET field1 = 1
> WHERE userid = 1234
> I am finding that *some* user id's result in the follwing error:
> "Arithmetic overflow error converting expression to data type
> smalldatetime.
> The statement has been terminated."
> mytable view has a number of data fileds. All the data fields are of type
> smalldatatime.
> When i compare the user record of a userid that causes an error to one
> that doesnt cause an error, the dates do vary, where some date fields have
> NULL's or correctly formated smalldatatime values (yes I know about the
> restriction of smalldatetime to range between 1900 and 2079).
> The odd thing is that even if i am updating a non-date field within the
> view, the above arithmetic error still occurs.
> My trigger looks like the following:
> --
> CREATE TRIGGER mytrigger ON mytableview
> INSTEAD OF UPDATE
> AS
> DECLARE @.mydate datetime
> SELECT @.mydate = GETDATE()
> UPDATE mytable SET
> field1 = ISNULL(inserted.field1, 0),
> field2 = ISNULL(inserted.field2, 0),
> field3 = ISNULL(inserted.field3, 0),
> date1 = inserted.date1,
> date2 = @.date+30
> FROM inserted
> WHERE mytable.userid = inserted.userid
> --
> Am i getting this error because i am mixing a date2 fiels (which is of
> type smalldatetime) with @.date (which is of type datetime) ?
> Any help most appreciated.
>|||thanks ill try that when i get to work
I hope its as obvious as changing smalldatetime to datetime!
cheers, peter

> Also, drop the SELECT @.mydate -- was just for testing. Basically using
> the SMALLDATETIME data type instead of DATETIME.
> HTH
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23tFJV0z0FHA.2312@.TK2MSFTNGP14.phx.gbl...
>

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

Help with an error I don''t understand

Hey everyone,

I'm trying to make an image appear differently depending on the values of certain data. The statement that I am using is:

= IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)>15,"redball",IIF(DATEDIFF("dd",Fields!LastBackupDate.Value,Now)<8,"greenball","yellowball"))

And the error that I get is:

[rsRuntimeErrorInExpression] The Value expression for the image ‘image4’ contains an error: Argument 'Interval' is not a valid value.

Preview complete -- 0 errors, 1 warnings

When I preview the report I just get the typical white box with a red x where the image should be. I don't understand.

Thanks in advance,

Keith

Nevermind, argument needs to be "d". Thanks anyway!

-Keith

sql

help with a 'simple' sql-statement

Hi there,
i have the following problem: in a database i have to detect if a certain
column in a certain table exists. I
use the function 'ColumnAlreadyExists'. If not i have to create that column
and then i have to fill this
column. This is my code:
****************************************
*********
-- Function to check whether a specific column exists in a table
CREATE FUNCTION ColumnAlreadyExists(@.TableName NVARCHAR(128),@.ColumnName
NVARCHAR(128))
RETURNS INTEGER --Returns 0 if column does not exist. Returns 1 if column
exists.
AS
BEGIN
--See if the Table already contains the column.
IF EXISTS
(SELECT * FROM SysObjects O INNER JOIN SysColumns C ON O.ID=C.ID
WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name=@.TableName
AND C.Name=@.ColumnName)
RETURN 1
--Table does not contain the column.
RETURN 0
END
GO
-- Add column DataId to table Data if necessary
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill column DataId for each row in the Data table
DECLARE @.index1 int, @.index2 int, @.datapk int
DECLARE DataID_Cursor CURSOR FOR
SELECT index1, index2, datapk
FROM data
OPEN DataID_Cursor
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
WHILE @.@.FETCH_STATUS = 0
BEGIN
UPDATE Data
set dataid = ( index1 * index2 )
WHERE datapk = @.datapk
FETCH NEXT FROM DataID_Cursor
INTO @.index1, @.index2, @.datapk
END
END
DROP FUNCTION ColumnAlreadyExists
****************************************
*********
The problem now is that when i want to fill the dataid column it does not
exists yet, cause there was no GO
yet. But when i put a GO between the creating of the column and the filling
of this column i don't know
anymore whether the column existed or not. So my question is: How can i do
the following:
IF .dbo.ColumnAlreadyExists('data','dataid')=0
BEGIN
ALTER TABLE [data]
ADD [dataid] [int] NULL
-- Fill the just created column dataid for each row in the Data table
END
Can anyone help me with this problem?
thanks,
Koert"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...

> Can anyone help me with this problem?
Maybe DECLARE a variable...?|||I already tried that but I can't use that variable after the GO to create th
e
column. I tried:
DECLARE @.result int
SELECT @.result = .dbo.ColumnAlreadyExists('data','dataid')
IF @.result = 0
BEGIN
ALTER TABLE [dta]
ADD [dataid][int] NULL
END
GO
if @.result = 0 <--This one is not declared anymore
BEGIN
--Fill column DataID
END
so this does not work or is there any other way to declare a kind of global
variable'
thanks,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:BD268A25-ABFF-4287-B76D-F39A8EB954D9@.microsoft.com...
>
> Maybe DECLARE a variable...?
>
>|||"Koert" <Koert@.discussions.microsoft.com> wrote in message
news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...

> so this does not work or is there any other way to declare a kind of
> global
> variable'
Hmm - loathe though I am to suggest it, I think your only option might be to
create a temporary table... Temporary tables are persistent within a
connection, so should survive the GO statement...|||i thought about that to, it seemed to be so easy... The only other way i can
think of is to check if the dataid column is filled in, if not fill it.
thanks for posting,
Koert
"Mark Rae" wrote:

> "Koert" <Koert@.discussions.microsoft.com> wrote in message
> news:69895818-6900-4683-B2F5-E85949E48FBF@.microsoft.com...
>
> Hmm - loathe though I am to suggest it, I think your only option might be
to
> create a temporary table... Temporary tables are persistent within a
> connection, so should survive the GO statement...
>
>

Help SQL Server 2000

When I have done to attach database at server with certain collation. So how
to change its collation like as i want?
Because I've tried to use "ALTER DATABASE ... COLLATE ...", But it doesn't
change all tables.
How to to change its collation like as i want so simple?When you say all tables, do you mean some columns. It may be more effective
to set up the db, with required collation and the migrate data
Jack Vamvas
___________________________________
Receive free SQL tips - www.ciquery.com/sqlserver.htm
"Bpk. Adi Wira Kusuma" <adi_wira_kusuma@.yahoo.com.sg> wrote in message
news:#D5vF2XXGHA.3328@.TK2MSFTNGP02.phx.gbl...
> When I have done to attach database at server with certain collation. So
how
> to change its collation like as i want?
> Because I've tried to use "ALTER DATABASE ... COLLATE ...", But it
doesn't
> change all tables.
> How to to change its collation like as i want so simple?
>