Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

Case Statement With Bigger than?

Hi

How can i create a case statement with a bigger and smaller than sign in it. I keep on getting an error.

Here is the piece of code i'm working on and simply enough, the idea of what i am trying to accomplish.

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN (BMI < 18) THEN 'Under Weight'

WHEN (BMI < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Any Help will be greatly appreciated

Kind Regards

Carel Greaves

Carel:

It looks to me like you have it correct; the only thing you might want to do is add an additonal line after your WHEN statements -- something like

Code Snippet

ELSE 'Over Weight'

Oh the problem is that you cannot reference this as 'BMI'; you need to write out the long version like:

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE BMI

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

Transact SQL does not have the ability to alias BMI as in your original statement as (I think) Oracle does.

|||

The "case" function has two formats. You can not use both at the same time.

case -- BMI

when BMI < 18 then 'Under Weight'

when BMI < 25 then 'Healthy Weight'

else NULL

end as 'BMI Grouping'

AMB

|||

Carel,

Expanding upon Alejandro's response, CASE works like this:

CASE {ItemToTest}

WHEN {Value} THEN [AlternativeValue}

END

or

CASE

WHEN {ItemToTest} equality {Values} THEN {AlternativeValue}

END

The second alternative has quite a bit of flexibility. Each WHEN can test different {ItemToTest}, and

{AlternativeValue} can return a different datatype that {ItemToTest}.

|||

And you are not allowed to use the Column Aliase name as expression of other column in the same select list..

You have to use the following query,

Code Snippet

SELECT Weight.Weight,

Height.Height,

(Weight.Weight/(Height.Height*Height.Height)) AS BMI,

CASE

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 18) THEN 'Under Weight'

WHEN ((Weight.Weight/(Height.Height*Height.Height)) < 25) THEN 'Healthy Weight'

END AS 'BMI Grouping'

|||Thanks, yet again i find all the answers that i'm looking for

Thanks Guys

Kind Regards

Carel Greaves

|||

You are welcome; it is always a pleasure working with you, Carel.

case statement problem

Hi all

I am having a small problem with the case statement,
I have two table, a status table and users table ( i have scripted them below)

create table users
(id int
, user_name char (10) )

insert into users (id, user_name)
values ( 1, 'bob')
insert into users (id, user_name)
values ( 2, 'sue')
insert into users (id, user_name)
values ( 3, 'richard')
insert into users (id, user_name)
values ( 4, 'john')
insert into users (id, user_name)
values ( 5, 'wendy')

create table status
(name char (10)
, status int, sales_manager int, account_manager int)

insert into status (name, status, sales_manager)
values ('test1', 1, 1 )
insert into status (name, status, sales_manager)
values ('test2', 1, 2 )
insert into status (name, status, account_manager)
values ('test3', 2, 3 )
insert into status (name, status, account_manager)
values ('test4', 2, 4 )
insert into status (name, status)
values ('test5', 2 )

What i need to do when i run the below statement it gives me a list of the names
and the managers, if there is a null value returned i want it to display
'No manager assigned' or something like that

select s.name
, 'manager' = case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

thanks

Like this, use COALESCE or ISNULL

select status,s.name
, 'manager' = case
when status = 1 then coalesce(u1.user_name,'No manager assigned')
when status = 2 then coalesce(u2.user_name,'No manager assigned')
else 'no'
end
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

You can do below:

select s.name
, coalesce(case
when status = 1 then u1.user_name
when status = 2 then u2.user_name
else 'no'
end, 'No manager assigned') as manager
from status as s
left join users as u1
on u1.id = s.sales_manager
left join users as u2
on u2.id = s.account_manager

Also, please don't use the 'column_alias' = expr syntax. This has been deprecated in SQL Server 2005 and will be removed in a future version of SQL Server. See link below for more details:

http://msdn2.microsoft.com/en-us/ms143729(SQL.90).aspx

|||Thanks guys for the answers, sorted!

Case Statement help

I am trying to create columns from calculations. Essentially what I want is
this:
Column1 = A then (Column2 - Column3) as ActualAmount else
Column1 = B then (Column2 - Column3) as BudgetAmount
Maybe I am not thinking straight, but this seems like it should be simple.
Here is what I started with:
Case When GLBA.ACTUAL_FLAG = 'A' Then
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
ActQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as ActBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
Else
GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
BudQtrBalance,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as BudBeginBalance,
GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
End
I thought Decode was the way to go but can't seem to make that work either.
Please and Thank you
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1David,
Use as an example - listing two columns because you have two column aliases
can account for NULLs if required.
Try:
CREATE TABLE TESTTABLE300
(COLUMN1 CHAR(1) NOT NULL,
COLUMN2 INT NOT NULL,
COLUMN3 INT NOT NULL)
INSERT TESTTABLE300
VALUES('A',1000,300)
INSERT TESTTABLE300
VALUES('B',1000,600)
SELECT COLUMN1,
CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
FROM TESTTABLE300
--DROP TABLE TESTTABLE300
HTH
Jerry
"David P via webservertalk.com" <u12188@.uwe> wrote in message
news:5653e494447eb@.uwe...
>I am trying to create columns from calculations. Essentially what I want is
> this:
> Column1 = A then (Column2 - Column3) as ActualAmount else
> Column1 = B then (Column2 - Column3) as BudgetAmount
> Maybe I am not thinking straight, but this seems like it should be simple.
> Here is what I started with:
> Case When GLBA.ACTUAL_FLAG = 'A' Then
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> ActQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> ActBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as ActEndBalance
> Else
> GLBA.QUARTER_TO_DATE_DR - GLBA.QUARTER_TO_DATE_CR as
> BudQtrBalance,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR as
> BudBeginBalance,
> GLBA.PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudPeriodNet,
> GLBA.BEGIN_BALANCE_DR - GLBA.BEGIN_BALANCE_CR + GLBA.
> PERIOD_NET_DR - GLBA.PERIOD_NET_CR as BudEndBalance
> End
> I thought Decode was the way to go but can't seem to make that work
> either.
> Please and Thank you
>
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200510/1|||So I just need to create a CASE statement for each calculation.
I'll give that a try.
Jerry Spivey wrote:
>David,
>Use as an example - listing two columns because you have two column aliases
>can account for NULLs if required.
>Try:
>CREATE TABLE TESTTABLE300
>(COLUMN1 CHAR(1) NOT NULL,
> COLUMN2 INT NOT NULL,
> COLUMN3 INT NOT NULL)
>INSERT TESTTABLE300
>VALUES('A',1000,300)
>INSERT TESTTABLE300
>VALUES('B',1000,600)
>SELECT COLUMN1,
> CASE COLUMN1 WHEN 'A' THEN (Column2 - Column3) END as ActualAmount,
> CASE COLUMN1 WHEN 'B' THEN (Column2 - Column3) END as BudgetAmount
>FROM TESTTABLE300
>--DROP TABLE TESTTABLE300
>HTH
>Jerry
>[quoted text clipped - 28 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200510/1

Case Statement

I am trying to create a statement that has a case in the where. I have it
working but when I add in a IN... to the case statement it doesn't return
anything.
Sample.
Select * from dbo.Sometable
Where SomeTable.SomeField IN (case @.Variable when 1 then 'sometext1' When
2 then 'sometext2' when 3 then 'sometext1, sometext2')
passing 1 works
passing 2 works
passing 3 nothing returns
Any Ideas?
Thanks,>> I am trying to create a statement that has a case in the where. I have i
t
working but when I add in a IN... to the case statement it doesn't
return
anything. <<
There is no CASE statement in SQL; there is a CASE expression! You are
also confusing columns and fields.
SELECT *
FROM Sometable
WHERE some_col
= (CASE @.variable
WHEN 1 THEN 'sometext1'
WHEN 2 THEN 'sometext2'
WHEN 3 THEN 'sometext1, sometext2'
ELSE NULL END);|||SQL is interpreting 'sometext1, sometext2' as a single string, and not
expanding it to a set the way you would like it to.
Try this instead:
DECLARE @.Variable int
Select * from dbo.Sometable
where
CASE
WHEN SomeTable.SomeField = 'sometext1' THEN 1
WHEN SomeTable.SomeField = 'sometext1' THEN 2
WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
ELSE 0
END = @.Variable
"Richard Thayne" wrote:

> I am trying to create a statement that has a case in the where. I have it
> working but when I add in a IN... to the case statement it doesn't return
> anything.
> Sample.
> Select * from dbo.Sometable
> Where SomeTable.SomeField IN (case @.Variable when 1 then 'sometext1' When
> 2 then 'sometext2' when 3 then 'sometext1, sometext2')
> passing 1 works
> passing 2 works
> passing 3 nothing returns
> Any Ideas?
> Thanks,
>
>|||Oops. What I meant was
DECLARE @.Variable int
Select * from dbo.Sometable
where
CASE
WHEN SomeTable.SomeField = 'sometext1' THEN 1
WHEN SomeTable.SomeField = 'sometext2' THEN 2 --correction made here
WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
ELSE 0
END = @.Variable
"Mark Williams" wrote:
> SQL is interpreting 'sometext1, sometext2' as a single string, and not
> expanding it to a set the way you would like it to.
> Try this instead:
> DECLARE @.Variable int
> Select * from dbo.Sometable
> where
> CASE
> WHEN SomeTable.SomeField = 'sometext1' THEN 1
> WHEN SomeTable.SomeField = 'sometext1' THEN 2
> WHEN SomeTable.SomeField IN ('sometext1','sometext2') THEN 3
> ELSE 0
> END = @.Variable
>
> --
> "Richard Thayne" wrote:
>|||Hello --CELKO--,
This did not work.

> working but when I add in a IN... to the case statement it doesn't
> return
> anything. <<
> There is no CASE statement in SQL; there is a CASE expression! You
> are also confusing columns and fields.
> SELECT *
> FROM Sometable
> WHERE some_col
> = (CASE @.variable
> WHEN 1 THEN 'sometext1'
> WHEN 2 THEN 'sometext2'
> WHEN 3 THEN 'sometext1, sometext2'
> ELSE NULL END);

Case Sensitivity on a non case sensitive DB

Hi,
I'm running into an issue with case sensitivity. Here is the setup: I have
SQL Server instance that is CS AS by default. Create new database that is
CI AS. Run a file of SQL against this database to create triggers and get
an error on a variable name in one of the triggers. The variable is
declared as @.szName but used as @.szname. I would have thought that since
this is a trigger on the CI database that it would not matter but the
trigger create must somehow interact with Master or MSDB and since they are
CS this causes a problem? Any options on how to easily make this go away
other than the obvious - changing all triggers (and I would guess stored
procedure code as well). Thanks in advance for any help.
Wayne Antinore> SQL Server instance that is CS AS by default. Create new database that is
> CI AS.
I think you should decide to either (a) use CS only or CI only or (b) use a
different instance for CI. Just wait until you start doing any work that
requires tempdb... you will get collation conflicts all over the place.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Thanks Aaron,
Yikes! Never even got into using tempdb yet. I can only imagine what I
would come across there.
Thanks again,
Wayne
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%23rGSiMsBEHA.1380@.TK2MSFTNGP10.phx.gbl...
is
> I think you should decide to either (a) use CS only or CI only or (b) use
a
> different instance for CI. Just wait until you start doing any work that
> requires tempdb... you will get collation conflicts all over the place.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>

Case sensetive column

I create an user table . I have an column userName . I want to make case sensetive to data of userName column .

Check out the the last post in this thread:http://forums.asp.net/p/1050454/1483491.aspx

Case Problem!

Hi all,
I am trying to create the following stored procedure..
I am still not all that familiar with the Case statement so any help would
be appreciated...!!!
I am getting a syntax errors...
Is there a better way to do this?
CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
@.area VARCHAR(120)
AS
BEGIN
Declare @.last_induction INT
SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
CASE
WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
VALUES(@.area)
WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
VALUES(@.area)
WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
VALUES(@.area)
WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
VALUES(@.area)
END
Cheers,
AdamCASE is an expression, not a statement; it is not used as a
control-of-flow element. To do what you're attempting to do, you'll
need a series of IF.. ELSE statements.
Of course, just from a cursory glance at your code, it appears that
your data model needs work; what do asmt_1, asmt_2... represent? Are
they different attributes of your entity, or are they simply holders
for value?
Stu|||The data model sucks i know,...
But the request from the powers that be, is that is must be that way..
Thanks for the info..
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>|||without nagging you about the schema and all...here is the insert without
if/else.
insert asmt_v1_cards(asmt_1,asmt_2,asmt_3,asmt_
4)
select case when @.last_induction is null then @.area end,
case when @.last_induction=1 then @.area end,
case when @.last_induction=2 then @.area end,
case when @.last_induction=3 then @.area end
-oj
"Mr Ideas Man" <adam@.pertrain.com.au> wrote in message
news:uoQRTyPOGHA.964@.tk2msftngp13.phx.gbl...
> Hi all,
> I am trying to create the following stored procedure..
> I am still not all that familiar with the Case statement so any help would
> be appreciated...!!!
> I am getting a syntax errors...
> Is there a better way to do this?
> CREATE PROCEDURE [dbo].[asmt_v1_ins_card_induction]
> @.area VARCHAR(120)
> AS
> BEGIN
> Declare @.last_induction INT
> SET @.last_induction = (SELECT last_induction FROM asmt_v1_cards )
> CASE
> WHEN @.last_induction = NULL THEN INSERT INTO asmt_v1_cards (asmt_1)
> VALUES(@.area)
> WHEN @.last_induction = 1 THEN INSERT INTO asmt_v1_cards (asmt_2)
> VALUES(@.area)
> WHEN @.last_induction = 2 THEN INSERT INTO asmt_v1_cards (asmt_3)
> VALUES(@.area)
> WHEN @.last_induction = 3 THEN INSERT INTO asmt_v1_cards (asmt_4)
> VALUES(@.area)
> END
> Cheers,
> Adam
>

CASE Problem in Stored Procedure

Can anyone see a problem with this stored procedure.
When I do this one it works fine.

CREATE Procedure SS_SoftList
(
@.CompanyID nvarchar(10),
@.Order varchar(20)
)
As

SELECT
SS_Soft_deploy.Softwarename,
( COUNT(SS_Soft_deploy.Softwarename)) as recordcount

FROM
SS_Soft_deploy

WHERE
SS_Soft_deploy.CompanyID = @.CompanyID

GROUP BY
SS_Soft_deploy.Softwarename

ORDER BY recordcount
--CASE WHEN @.Order = 'softname' THEN Softwarename END,
--CASE WHEN @.Order = 'count' THEN recordcount END
GO

However when I un-rem the CASE statement
ORDER BY
CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN recordcount END
GO

It reports an error that says "Invalid Column Name recordcount"
Any Ideas??Recordcount is an alias. You cannot sort on that using the alias.

CASE WHEN @.Order = 'softname' THEN Softwarename END,
CASE WHEN @.Order = 'count' THEN ( COUNT(SS_Soft_deploy.Softwarename)) END

|||Why does it work when I do the
ORDER BY recordcount
??|||Hmm. I did not realize that would work using the alias in the ORDER BY, but apparently it does. It does not work inside the case statement because that is a different expression, and that expression is unaware of the alias. Repeating the expression, however, works fine. If the expression were terribly complex, you could alternately create a user defined function and use that for the row and the ORDER BY (knowing that performance would likely be less than great).

CASE in where clause?

I want to create SQL (maybe stored proc) using a SELECT statement that can
add WHERE conditions if (and only if) values are sent to it. For example:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%@.var1%' +
CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
ELSE ''
END
...etc
Is this possible? Thanks
David> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%@.var1%' +
> CASE WHEN @.var2 IS NOT NULL THEN ' AND TypeCode = ' + @.var2
> ELSE ''
> END
Did you mean:
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = COALESCE(@.TypeCode, TypeCode)
or more verbosely
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND TypeCode = CASE WHEN @.TypeCode IS NULL THEN TypeCode ELSE @.TypeCode END
CASE is an expression that returns a single value. It is *NOT* used for
flow control.|||could I suggest a slight amendment (and add a question):
SELECT ID, Person
FROM People
WHERE LastName LIKE '%' + @.var1 + '%'
AND (@.TypeCode is null OR TypeCode = @.TypeCode)
this way when @.typecode is null the lazy evaluation of SQL will mean it
won't even look at the second statement.
I've found if it's put the other way:
WHERE Typecode = @.TypeCode OR @.TypeCode is null
it will first evaluation Typecode = @.TypeCode, causing it to scan that
field, then it will evaluate the second statement.
I'm not sure but I suspect your suggestion of Typecode =
COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
even when a null value is passed to it.
Cheers
Will|||> I've found if it's put the other way:
> WHERE Typecode = @.TypeCode OR @.TypeCode is null
> it will first evaluation Typecode = @.TypeCode, causing it to scan that
> field, then it will evaluate the second statement.
There is no guarantee in the order of execution within a clause, it could go
left to right, it could go right to left.

> I'm not sure but I suspect your suggestion of Typecode =
> COALLESCE(@.TypeCode, TypeCode) will cause it to read the typecode field
> even when a null value is passed to it.
Really hard to say without table structure, sample data, row counts, and the
ability to actually test and examine query plans.|||"There is no guarantee in the order of execution within a clause, it
could go
left to right, it could go right to left. "
I know you're an MVP, and do indeed know SQL server way better than me,
but still I feel I have to ask - are you sure about this?
it's just that I've found on several occasions that if I switch the 2
statements around it changes the execution plan fundamentally, and
doesn't do any table scans (when the value is null of course). If the
order was not an issue then surely switching the order would not affect
the query?
Thanks
Will|||> it's just that I've found on several occasions that if I switch the 2
> statements around it changes the execution plan fundamentally, and
> doesn't do any table scans (when the value is null of course). If the
> order was not an issue then surely switching the order would not affect
> the query?
I didn't say order CAN'T be an issue. Just don't rely on this kind of
short-circuiting to work consistently.|||Fundamental mistake! There is no CASE **statement** in SQL. There is
a CASE **expression**; remember programming 101? Expressions return
scalar values, not control of execution flow.
SELECT person_id, person_name
FROM People
WHERE last_name LIKE '%' + @.var1 + '%'
AND foobar_code = COALESCE(@.my_code, foobar_code) ;
Even for an example, you had some pretty awful data element names.
Something can be a type of something or a code. It cannot be both.
There is no such thing as just an "id" -- it has to identify something
in particular.
You might want to get a book on SQL and data modeling.|||Will (william_pegg@.yahoo.co.uk) writes:
> could I suggest a slight amendment (and add a question):
> SELECT ID, Person
> FROM People
> WHERE LastName LIKE '%' + @.var1 + '%'
> AND (@.TypeCode is null OR TypeCode = @.TypeCode)
> this way when @.typecode is null the lazy evaluation of SQL will mean it
> won't even look at the second statement.
It's unclear here what you mean with "second statement".
@.TypeCode IS NULL OR TypeCode = @.TypeCode
would be the same as
TypeCode = @.TypeCode OR @.TypeCode IS NULL
when it comes to performance.
If you've seen something else, it might have been a mirage due to
caching.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||My Apologies,
I really should stop questioning MVPs, you guys don't ever seem to be
wrong. I tried to recreate the situation and couldn't. My reasoning was
based on several queries I've debugged in the past that were displaying
a strange execution plan, and then by switching the statements around
(the "or" statements), increased performance dramatically (from 4s to
50ms). I assumed at the time that it was just a simple grammar case
that you could apply lazy evaluation, and that was how SQL was
optimising it, however it appears that there's more stuff going on.
perhaps the optimiser was having an odd day, or more likely there were
incorrect index statistics monkeying things up, but anyway, thanks for
clearing it up.
Cheers
Will|||The problem is that SQL is set-oriented and not sequential. The THEN
clauses in a CASE expression (which includes COALESCE()) all have to be
evaluated to determine the data type of the whole expression. It does
not matter if some of them are unreachable.
COALESCE correctly promotes its arguments to the highest data type in
the expression:
13 / COALESCE(CAST(NULL AS INTEGER), 2.00) = 6.5
The proprietary ISNULL() uses the first data type and gets things wrong
13 / ISNULL(CAST(NULL AS INTEGER), 2.00) = 6
You would need to write:
13 / ISNULL(CAST(NULL AS DECIMAL(4,2)), 2.00)sql

Case Expression within a Stored Proc

Is it possible? I have a request to create a stored proc that will
dynamically add a range to a WHERE clause based on a numeric value of a
comment type. If the incoming comment type request is say 10, the
where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
a 20 is passed in the clause would read IN(20,21....)
So I was thinking that a CASE expression within the proc would be the
best way to go, but have had no luck in finding an example or any other
related information regarding CASE exp in a proc.
TIA
BillOn 8 Sep 2004 06:44:18 -0700, Bill Willyerd wrote:

>Is it possible? I have a request to create a stored proc that will
>dynamically add a range to a WHERE clause based on a numeric value of a
>comment type. If the incoming comment type request is say 10, the
>where clause needs to be set to IN(10,11,12,13,14,15,16,17,18,19)OR if
>a 20 is passed in the clause would read IN(20,21....)
>So I was thinking that a CASE expression within the proc would be the
>best way to go, but have had no luck in finding an example or any other
>related information regarding CASE exp in a proc.
>TIA
>Bill

Hi Bill,

In this case (no pun intended), I'd simply write it like this:

WHERE MyColumn BETWEEN @.parameter AND @.parameter + 9

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 08:17:57 -0700, Bill Willyerd wrote:

>Sorry I didn't add that if a request for specfic comment type comes in
>like 22 we only return the type 22's.
>I do like the BETWEEN stmt though I haven't seen that before, I will
>remember that one.
>Thx, Bill

Hi Bill,

Is the request for "a specific comment type" passed in through a seperate
parameter? And the other parameter is used to get a range of comment
types?

CREATE PROC MyProc @.Specific int,
@.RangeStart int
AS
IF (@.Specific IS NULL AND @.RangeStart IS NULL)
OR (@.Specific IS NOT NULL AND @.RangeStart IS NOT NULL)
RAISERROR ('Supply exactly one of the two parameters', 16, 1)
ELSE
IF @.Specific IS NOT NULL
SELECT Column List
FROM YourTable
WHERE MyColumn = @.Specific
ELSE
SELECT Column List
FROM YourTable
WHERE MyColumn BETWEE @.RangeStart AND @.RangeStart + 9
go
(untested)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 8 Sep 2004 16:28:26 -0700, Bill Willyerd wrote:

>At present it comes as a single request.
(snip)

Hi Bill,

How do you know if the request is for a specific comment type or for a
range? Surely, there has to be some way to distinguish a request for
comment type '20' (meaning just 20) from a request for comment type '20'
(meaning all values 20 through 29).

Maybe this is a good time to explain what information should be included
in newsgroup postings to maximise the chance to get a useful reply:

* Table structure, posted as DDL (CREATE TABLE statements, omitting
irrelevant columns but including all constraints);
* Sample data, posted as INSERT statements (and please verify that the
CREATE TABLE and INSERT statements you post work properly in an empty test
database!);
* Expected output, based on sample data;
* The SQL code you already got (if any), plus the results these give you
and the reason why that is wrong. If you get an error message, copy and
paste the full message;
* A short, concise description of the business problem you're trying to
solve.

Check out these sites as well:
http://www.aspfaq.com/etiquette.asp?id=5006
http://vyaskn.tripod.com/code.htm#inserts

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

CASE and DATE statement - Need Help :)

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

Quote:

Originally Posted by opusid

Hi

Can someone help please.

I need to create a new column with a name of "AdvertPurchasePrice" based upon a Date Value which is held in a column called "AdvertCreationDate".

If the date is before the 10th of April 2007 then the column "AdvertPurchasePrice" needs to be 299 else it needs to be 399

Can anyone help?

Steve

use the CASE...WHEN...END statement...

select AdvertPurchasePrice = case when AdvertCreationDate is before 04/10/2007 then 299
else 399
end
from mytable

this is a pseudocode, not a working code...but if you search the help on the CASE-WHEN-END syntax, you'll see what i mean

Cascading Parameters in Report Builder Reports!

Is it possible to create cascading parameters with in Report Builder? I have done this with Report Designer but i need a solution with Report Builder.

(I need the structure: First the user selects the state, afer the postpack the cirties of the state are listed in another dropdownlist for selection).

Thanks in advance

I need to know if it is possible. I have checked Report Builder and didn't find a way for it.

I am curios if i have missed something or if Report Builder doesn't support cascadign parameters.

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
************************************************** ********
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
Jamie
Jamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> ************************************************** ********
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie
|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
************************************************** ********[vbcol=seagreen]
CLUSTERED[vbcol=seagreen]
DealAttribute[vbcol=seagreen]
key
>
>.
>
|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...[vbcol=seagreen]
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> problem
> the table with two
> DELETE CASCADE ON
> message
> ************************************************** ********
> CLUSTERED
> DealAttribute
> key
|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
to[vbcol=seagreen]
ON[vbcol=seagreen]
error[vbcol=seagreen]
UPDATE[vbcol=seagreen]
************************************************** ********[vbcol=seagreen]
Deal[vbcol=seagreen]
Delete/Update[vbcol=seagreen]
the[vbcol=seagreen]
SQL I
>
>.
>
|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:

>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
**********************************************************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE ON
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> **********************************************************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
>)
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
>)
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> I cannot understand why I receive the following error
>> mesage when trying to Create a cascading delete
>> constraint.
>> Introducing FOREIGN KEY
>> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> table 'DealAttribute' may cause cycles or multiple
>> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
>> NO ACTION, or modify other FOREIGN KEY constraints.
>> Deal Table
>> CREATE TABLE [Deal] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [Reference] [char] (20),
>> [CptyID] [int] NULL
>> ............
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> (
>> [DealID],
>> [Generation])
>>
**********************************************************
>> ******
>> Deal Attribute Table
>> CREATE TABLE [DealAttribute] (
>> [DealID] [int] NOT NULL ,
>> [Generation] [int] NOT NULL ,
>> [AttributeID] [int] NOT NULL ,
>> ............
>> ............
>> ............
>> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
CLUSTERED
>> (
>> [DealID],
>> [Generation],
>> [AttributeID])
>> I then want to add a constraint on the DealAttribute
>> table that references DealID and Generation in the Deal
>> table. I want the constraint to cascade Delete/Update
>> i.e. when I delete a record from the deal table for the
>> corresponding record to be removed from the
DealAttribute
>> table. But I get the above error. Below is the SQL I
>> use to create the Constraint.
>> if exists (select 1
>> from sysobjects
>> where id = object_id
>> ('FK_DEALATTR_RELATION__DEAL')
>> and type = 'FK')
>> alter table DealAttribute
>> drop constraint FK_DEALATTR_RELATION__DEAL
>> go
>> alter table DealAttribute
>> add constraint FK_DEALATTR_RELATION__DEAL foreign
key
>> (DealID, Generation)
>> references Deal (DealID, Generation)
>> on update cascade on delete cascade
>> go
>> I dont see how it is cyclic.
>> Please help.
>> Thanks
>> Jamie
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> >--Original Message--
> >Jamie
> >Read the error message which says what is exactly the
> problem
> >
> >You have tried to create a constraint that refernces to
> the table with two
> >columns(DealID, Generation)
> >The below script will work for you
> >
> >CREATE TABLE Parent
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > [NAME]CHAR(1) NOT NULL
> >)
> >INSERT INTO Parent VALUES (1,'A')
> >INSERT INTO Parent VALUES (2,'B')
> >INSERT INTO Parent VALUES (3,'C')
> >
> >CREATE TABLE Child
> >(
> > [ID] INT NOT NULL PRIMARY KEY,
> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
> DELETE CASCADE ON
> >UPDATE CASCADE,
> > [NAME]CHAR(2) NOT NULL
> >)
> >
> >INSERT INTO Child VALUES (1,1,'AA')
> >INSERT INTO Child VALUES (2,1,'AA')
> >INSERT INTO Child VALUES (3,2,'BB')
> >INSERT INTO Child VALUES (4,2,'BB')
> >INSERT INTO Child VALUES (5,2,'BB')
> >INSERT INTO Child VALUES (6,3,'CC')
> >
> >
> >
> >
> >
> >
> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
> >> I cannot understand why I receive the following error
> >> mesage when trying to Create a cascading delete
> >> constraint.
> >>
> >> Introducing FOREIGN KEY
> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
> >> table 'DealAttribute' may cause cycles or multiple
> >> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> >> NO ACTION, or modify other FOREIGN KEY constraints.
> >>
> >> Deal Table
> >>
> >> CREATE TABLE [Deal] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [Reference] [char] (20),
> >> [CptyID] [int] NULL
> >> ............
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> >> (
> >> [DealID],
> >> [Generation])
> >>
> >>
> **********************************************************
> >> ******
> >>
> >> Deal Attribute Table
> >>
> >> CREATE TABLE [DealAttribute] (
> >> [DealID] [int] NOT NULL ,
> >> [Generation] [int] NOT NULL ,
> >> [AttributeID] [int] NOT NULL ,
> >> ............
> >> ............
> >> ............
> >>
> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
> CLUSTERED
> >> (
> >> [DealID],
> >> [Generation],
> >> [AttributeID])
> >>
> >> I then want to add a constraint on the DealAttribute
> >> table that references DealID and Generation in the Deal
> >> table. I want the constraint to cascade Delete/Update
> >> i.e. when I delete a record from the deal table for the
> >> corresponding record to be removed from the
> DealAttribute
> >> table. But I get the above error. Below is the SQL I
> >> use to create the Constraint.
> >>
> >> if exists (select 1
> >> from sysobjects
> >> where id = object_id
> >> ('FK_DEALATTR_RELATION__DEAL')
> >> and type = 'FK')
> >> alter table DealAttribute
> >> drop constraint FK_DEALATTR_RELATION__DEAL
> >> go
> >>
> >> alter table DealAttribute
> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
> key
> >> (DealID, Generation)
> >> references Deal (DealID, Generation)
> >> on update cascade on delete cascade
> >> go
> >>
> >> I dont see how it is cyclic.
> >>
> >> Please help.
> >> Thanks
> >> Jamie
> >
> >
> >.
> >|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
>)
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
>news:267be01c46290$e4e593c0$a501280a@.phx.gbl...
>> Uri,
>> I reference the two columns because the combination of
>> the two make a unique key and are the PK for both
>> tables. Am I missing something really obvious here?
>> Thanks
>> Jamie
>> >--Original Message--
>> >Jamie
>> >Read the error message which says what is exactly the
>> problem
>> >
>> >You have tried to create a constraint that refernces
to
>> the table with two
>> >columns(DealID, Generation)
>> >The below script will work for you
>> >
>> >CREATE TABLE Parent
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > [NAME]CHAR(1) NOT NULL
>> >)
>> >INSERT INTO Parent VALUES (1,'A')
>> >INSERT INTO Parent VALUES (2,'B')
>> >INSERT INTO Parent VALUES (3,'C')
>> >
>> >CREATE TABLE Child
>> >(
>> > [ID] INT NOT NULL PRIMARY KEY,
>> > GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])
ON
>> DELETE CASCADE ON
>> >UPDATE CASCADE,
>> > [NAME]CHAR(2) NOT NULL
>> >)
>> >
>> >INSERT INTO Child VALUES (1,1,'AA')
>> >INSERT INTO Child VALUES (2,1,'AA')
>> >INSERT INTO Child VALUES (3,2,'BB')
>> >INSERT INTO Child VALUES (4,2,'BB')
>> >INSERT INTO Child VALUES (5,2,'BB')
>> >INSERT INTO Child VALUES (6,3,'CC')
>> >
>> >
>> >
>> >
>> >
>> >
>> >"Jamie" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:2670701c4628c$11b4edb0$a401280a@.phx.gbl...
>> >> I cannot understand why I receive the following
error
>> >> mesage when trying to Create a cascading delete
>> >> constraint.
>> >>
>> >> Introducing FOREIGN KEY
>> >> constraint 'FK_DEALATTR_RELATION__DEAL' on
>> >> table 'DealAttribute' may cause cycles or multiple
>> >> cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE
>> >> NO ACTION, or modify other FOREIGN KEY constraints.
>> >>
>> >> Deal Table
>> >>
>> >> CREATE TABLE [Deal] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [Reference] [char] (20),
>> >> [CptyID] [int] NULL
>> >> ............
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation])
>> >>
>> >>
**********************************************************
>> >> ******
>> >>
>> >> Deal Attribute Table
>> >>
>> >> CREATE TABLE [DealAttribute] (
>> >> [DealID] [int] NOT NULL ,
>> >> [Generation] [int] NOT NULL ,
>> >> [AttributeID] [int] NOT NULL ,
>> >> ............
>> >> ............
>> >> ............
>> >>
>> >> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY
>> CLUSTERED
>> >> (
>> >> [DealID],
>> >> [Generation],
>> >> [AttributeID])
>> >>
>> >> I then want to add a constraint on the DealAttribute
>> >> table that references DealID and Generation in the
Deal
>> >> table. I want the constraint to cascade
Delete/Update
>> >> i.e. when I delete a record from the deal table for
the
>> >> corresponding record to be removed from the
>> DealAttribute
>> >> table. But I get the above error. Below is the
SQL I
>> >> use to create the Constraint.
>> >>
>> >> if exists (select 1
>> >> from sysobjects
>> >> where id = object_id
>> >> ('FK_DEALATTR_RELATION__DEAL')
>> >> and type = 'FK')
>> >> alter table DealAttribute
>> >> drop constraint FK_DEALATTR_RELATION__DEAL
>> >> go
>> >>
>> >> alter table DealAttribute
>> >> add constraint FK_DEALATTR_RELATION__DEAL foreign
>> key
>> >> (DealID, Generation)
>> >> references Deal (DealID, Generation)
>> >> on update cascade on delete cascade
>> >> go
>> >>
>> >> I dont see how it is cyclic.
>> >>
>> >> Please help.
>> >> Thanks
>> >> Jamie
>> >
>> >
>> >.
>> >
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, when I realised I was writting utter rubbish. Check to see that you SQL Server is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK were created without problem. I also poped a couple of rows in the table, and the cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:
>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Cascade Deletes

I cannot understand why I receive the following error
mesage when trying to Create a cascading delete
constraint.
Introducing FOREIGN KEY
constraint 'FK_DEALATTR_RELATION__DEAL' on
table 'DealAttribute' may cause cycles or multiple
cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
NO ACTION, or modify other FOREIGN KEY constraints.
Deal Table
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL
............
............
............
............
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation])
****************************************
******************
******
Deal Attribute Table
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
............
............
............
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
(
[DealID],
[Generation],
[AttributeID])
I then want to add a constraint on the DealAttribute
table that references DealID and Generation in the Deal
table. I want the constraint to cascade Delete/Update
i.e. when I delete a record from the deal table for the
corresponding record to be removed from the DealAttribute
table. But I get the above error. Below is the SQL I
use to create the Constraint.
if exists (select 1
from sysobjects
where id = object_id
('FK_DEALATTR_RELATION__DEAL')
and type = 'FK')
alter table DealAttribute
drop constraint FK_DEALATTR_RELATION__DEAL
go
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL foreign key
(DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go
I dont see how it is cyclic.
Please help.
Thanks
JamieJamie
Read the error message which says what is exactly the problem
You have tried to create a constraint that refernces to the table with two
columns(DealID, Generation)
The below script will work for you
CREATE TABLE Parent
(
[ID] INT NOT NULL PRIMARY KEY,
[NAME]CHAR(1) NOT NULL
)
INSERT INTO Parent VALUES (1,'A')
INSERT INTO Parent VALUES (2,'B')
INSERT INTO Parent VALUES (3,'C')
CREATE TABLE Child
(
[ID] INT NOT NULL PRIMARY KEY,
GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON DELETE CASCADE O
N
UPDATE CASCADE,
[NAME]CHAR(2) NOT NULL
)
INSERT INTO Child VALUES (1,1,'AA')
INSERT INTO Child VALUES (2,1,'AA')
INSERT INTO Child VALUES (3,2,'BB')
INSERT INTO Child VALUES (4,2,'BB')
INSERT INTO Child VALUES (5,2,'BB')
INSERT INTO Child VALUES (6,3,'CC')
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
> I cannot understand why I receive the following error
> mesage when trying to Create a cascading delete
> constraint.
> Introducing FOREIGN KEY
> constraint 'FK_DEALATTR_RELATION__DEAL' on
> table 'DealAttribute' may cause cycles or multiple
> cascade paths. Specify ON DELETE NO ACTION or ON UPDATE
> NO ACTION, or modify other FOREIGN KEY constraints.
> Deal Table
> CREATE TABLE [Deal] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [Reference] [char] (20),
> [CptyID] [int] NULL
> ............
> ............
> ............
> ............
> CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation])
> ****************************************
******************
> ******
> Deal Attribute Table
> CREATE TABLE [DealAttribute] (
> [DealID] [int] NOT NULL ,
> [Generation] [int] NOT NULL ,
> [AttributeID] [int] NOT NULL ,
> ............
> ............
> ............
> CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
> (
> [DealID],
> [Generation],
> [AttributeID])
> I then want to add a constraint on the DealAttribute
> table that references DealID and Generation in the Deal
> table. I want the constraint to cascade Delete/Update
> i.e. when I delete a record from the deal table for the
> corresponding record to be removed from the DealAttribute
> table. But I get the above error. Below is the SQL I
> use to create the Constraint.
> if exists (select 1
> from sysobjects
> where id = object_id
> ('FK_DEALATTR_RELATION__DEAL')
> and type = 'FK')
> alter table DealAttribute
> drop constraint FK_DEALATTR_RELATION__DEAL
> go
> alter table DealAttribute
> add constraint FK_DEALATTR_RELATION__DEAL foreign key
> (DealID, Generation)
> references Deal (DealID, Generation)
> on update cascade on delete cascade
> go
> I dont see how it is cyclic.
> Please help.
> Thanks
> Jamie|||Uri,
I reference the two columns because the combination of
the two make a unique key and are the PK for both
tables. Am I missing something really obvious here?
Thanks
Jamie
>--Original Message--
>Jamie
>Read the error message which says what is exactly the
problem
>You have tried to create a constraint that refernces to
the table with two
>columns(DealID, Generation)
>The below script will work for you
>CREATE TABLE Parent
>(
> [ID] INT NOT NULL PRIMARY KEY,
> [NAME]CHAR(1) NOT NULL
> )
>INSERT INTO Parent VALUES (1,'A')
>INSERT INTO Parent VALUES (2,'B')
>INSERT INTO Parent VALUES (3,'C')
>CREATE TABLE Child
>(
> [ID] INT NOT NULL PRIMARY KEY,
> GFID INT NOT NULL FOREIGN KEY REFERENCES Parent([ID])ON
DELETE CASCADE ON
>UPDATE CASCADE,
> [NAME]CHAR(2) NOT NULL
> )
>INSERT INTO Child VALUES (1,1,'AA')
>INSERT INTO Child VALUES (2,1,'AA')
>INSERT INTO Child VALUES (3,2,'BB')
>INSERT INTO Child VALUES (4,2,'BB')
>INSERT INTO Child VALUES (5,2,'BB')
>INSERT INTO Child VALUES (6,3,'CC')
>
>
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:2670701c4628c$11b4edb0$a401280a@.phx
.gbl...
****************************************
******************[vbcol=seagreen]
CLUSTERED[vbcol=seagreen]
DealAttribute[vbcol=seagreen]
key[vbcol=seagreen]
>
>.
>|||Jamie
Look at this helps you.
CREATE TABLE Test
(
col1 INT NOT NULL REFERNCES Table (col1),
col2 INT NOT NULL REFERNCES Table1 (col2),
Primary key (col,col2)
)
"Jamie" <anonymous@.discussions.microsoft.com> wrote in message
news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...[vbcol=seagreen]
> Uri,
> I reference the two columns because the combination of
> the two make a unique key and are the PK for both
> tables. Am I missing something really obvious here?
> Thanks
> Jamie
> problem
> the table with two
> DELETE CASCADE ON
> message
> ****************************************
******************
> CLUSTERED
> DealAttribute
> key|||Uri,
This is no good. Because there are multiple DealIDs with
different Generations in the DealAttribute table.
So if you deleted DealID from Deal table all DealID's
would go in DealAttribute table. Regardless of
generation. The two columns are a compund key. Is it
not possible to have a cascade delete with a compound key?
Cheers
Jamie...
>--Original Message--
>Jamie
>Look at this helps you.
>
>CREATE TABLE Test
>(
> col1 INT NOT NULL REFERNCES Table (col1),
> col2 INT NOT NULL REFERNCES Table1 (col2),
> Primary key (col,col2)
> )
>
>"Jamie" <anonymous@.discussions.microsoft.com> wrote in
message
> news:267be01c46290$e4e593c0$a501280a@.phx
.gbl...
to[vbcol=seagreen]
ON[vbcol=seagreen]
error[vbcol=seagreen]
UPDATE[vbcol=seagreen]
****************************************
******************[vbcol=seagreen]
Deal[vbcol=seagreen]
Delete/Update[vbcol=seagreen]
the[vbcol=seagreen]
SQL I[vbcol=seagreen]
>
>.
>|||Hi,
I started explaining what was wrong with what you were attempting to do, whe
n I realised I was writting utter rubbish. Check to see that you SQL Server
is up to date, patch wise.
I ran the following SQL, which is basically yours, and the tables and FK wer
e created without problem. I also poped a couple of rows in the table, and t
he cascade worked. My SQL Server version is 8.00.818.
Al
CREATE TABLE [Deal] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[Reference] [char] (20),
[CptyID] [int] NULL,
CONSTRAINT [PK_DEAL] PRIMARY KEY CLUSTERED
([DealID], [Generation])
)
GO
CREATE TABLE [DealAttribute] (
[DealID] [int] NOT NULL ,
[Generation] [int] NOT NULL ,
[AttributeID] [int] NOT NULL ,
CONSTRAINT [PK_DEALATTRIBUTE] PRIMARY KEY CLUSTERED
([DealID], [Generation], [AttributeID])
)
alter table DealAttribute
add constraint FK_DEALATTR_RELATION__DEAL
foreign key (DealID, Generation)
references Deal (DealID, Generation)
on update cascade on delete cascade
go|||On Mon, 5 Jul 2004 06:40:02 -0700, Jamie wrote:

>Uri,
>This is no good. Because there are multiple DealIDs with
>different Generations in the DealAttribute table.
>So if you deleted DealID from Deal table all DealID's
>would go in DealAttribute table. Regardless of
>generation. The two columns are a compund key. Is it
>not possible to have a cascade delete with a compound key?
>Cheers
>Jamie...
Hi Jamie,
That is possible. There must be another problem.
After reading your post, I had the idea that something was missing. Al's
post confirmed this.
I think that there is already an FK relation with some cascading option
between Deal and DealAttribute. It might even be an indirect relation
(e.g. from Deal to XYZ and from XYZ to DealAttribute). You might want to
check into that.
If you're sure that this is not a case, we need a way to reproduce your
problem. If you can post some CREATE TABLE and ALTER TABLE statements that
will reproduce your problem in an empty database (you can find out for
yourself by creating a play database, running the script in that database,
then dropping the play database again), we can investigate this further.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be do
ne through the GUI in Enterprise Manager. I CAN create a FK through the Ente
rprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box
"check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI a
nd not check existing data on creation?
If not how can I modify my script below to not check the data on creating th
e Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi,
I have a table that I want to create a Foreign Key constraint on.
This column has NULL values.
I want to create the Foreign Key with a CASCADE DELETE NO ACTION.
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on cre
ation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation?
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working.
Maybe I should have a Trigger instead?
Any advice/info is much appreciated.
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
(
[WKF_WORKFLOW_ID]
) REFERENCES [dbo].[T_NWKF_WORKFLOW] (
[WKF_WORKFLOW_ID]
)
ON DELETE NO ACTION
GO
Thanks,
C.
On Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

CASCADE DELETE (No Action)

Hi
I have a table that I want to create a Foreign Key constraint on
This column has NULL values
I want to create the Foreign Key with a CASCADE DELETE NO ACTION
I have done this through the script below as I am not sure if this can be done through the GUI in Enterprise Manager. I CAN create a FK through the Enterprise Manager GUI for a CASCADE ON DELETE UPDATE and uncheck the check box "check existing data on creation" and it works fine. Can I do a "ON DELETE NO ACTION" through the GUI and not check existing data on creation
If not how can I modify my script below to not check the data on creating the Foreign Key as this is why I think my Script is not working
Maybe I should have a Trigger instead
Any advice/info is much appreciated
Here is my script I wrote...
ALTER TABLE [dbo].[T_CMT_CONTENT] ADD
CONSTRAINT [FK_T_CMT_CONTENT_T_NWKF_WORKFLOW] FOREIGN KEY
[WKF_WORKFLOW_ID
) REFERENCES [dbo].[T_NWKF_WORKFLOW]
[WKF_WORKFLOW_ID
ON DELETE NO ACTION
G
Thanks
COn Thu, 27 May 2004 09:21:06 -0700, C wrote:
(snip)
Hi C,
Answered in microsoft.public.sqlserver.programming.
Please don't crosspost!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Carriage return in header of Flat File Destination

I'm trying to create a flat file that has a header like:

/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100
data,data,data,data
data,data,data,data

where 'data' represents the data written out by the data flow process to the flat file destination. This actually turns out quite nice except that when I place the lines that start with '/' in the header box for the flat file destination the carriage return doesn't get written correctly after each line and I end up with an unrecognized character when I open the file in a simple app like notepad. I've tried using different encodings for the flat file connection, but to no avail. It is also interesting to note that when I close the package and reopen it the flat file destination editor UI also doesn't recognize the carriage returns and places a box in there place.

Below is a copy of the the property as it is written in the package xml:

<property id="92" name="Header" dataType="System.String" state="default" isArray="false" description="Specifies the text to write to the destination file before any data is written." typeConverter="" UITypeEditor="" containsID="false" expressionType="Notify">/INST=-1
/DELIMITER=","
/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4
/LOCATION=100</property>

Any help is appreciated.

-dotnetwiz

I was able to do this using a property expression on the 'header' property (accessed via expressions of the dataflow task).
When I reversed the order of \r\n to \n\r I do get some messages about inconsistent line delimeters in some editors.

"/INST=-1\r\n"
+"/DELIMITER=\",\"\r\n"
+"/FIELDS=FIELD1,FIELD2,FIELD3,FIELD4\r\n"
+"/LOCATION=100\r\n"

Hope this helps

|||How do you get to the "Expression" of the DataFlow task? Right-click does not list "expressions" as a menu item. The Advanced Editor does not provide any apparent access to "Expressions"...?|||In the control flow, right-click on the data flow task and select properties. Scroll down in that list and you'll see "Expressions."|||I'm trying to do something similar in setting up a header for a fixed width flat-file output. When I try to use \r\n after my text, the characters "\r\n" just show up in the header. How do I get a CR+LF? I've tried using ="mytext\r\n" and I just see that entire literal string, including the quotes, appear in the output.|||As Phil pointed out, on the Control Flow tab, you have a DataFlow component (which, when you edit it, leads to the DataFlow tab and displays components there). If you look at the properties for the object on the control tab, one of them is "Expressions" and you can open it to get at the properties of the components on the Dataflow tab (like header for a flat file destination.)

Setting the Expression to a quoted string allows you to include \r\n and they will be translated properly.