Question about self referencing delete

Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. (Isn't
it?) And then SQLServer is supposed to give an error ; as empid = 1 is being
referred as foreign key in record where empid is 2. But it doesn't happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where empid
is 1? (In short, deletes all cascading child records first and then parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
PravinThis is a multi-part message in MIME format.
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_0030_01C3745C.97B12C90
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

---

--=_NextPart_000_0030_01C3745C.97B12C90--|||This is a multi-part message in MIME format.
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did not =specify ON DELETE CASCADE in your foreign key". Does that mean SQLServer =allows setting the option 'on delete cascade' for self referencing keys? =If yes, then please inform me how. If no, then your answer that "In your =DELETE, you specified that you were deleting both the child and the =related parent, so there would be no RI violation." is just the answer =for my question. Right?
Note: I was not able to set this option from enterprize manager. Please =guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D 1 =is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer first
deletes record where empid is 2. And then it deletes the record where =empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000F_01C37454.B43BD200
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should avoid =using EM for creating tables. I stick with scripting it out and then saving =the scripts under version control.
-- Tom
----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.
However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting the =option 'on delete cascade' for self referencing keys? If yes, then please inform me =how. If no, then your answer that "In your DELETE, you =specified that you were deleting both the child and the related parent, so there would =be no RI violation." is just the answer for my question. =Right?
Note: I was not able to set this =option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify ON =DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that you =were deleting both the child and the related parent, so there would be no =RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE empid ==3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary key =for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM emp_1 =WHERE empid in (1, 2)So SQLSerever, will first attempt to delete =record where empid is 1. (Isn'tit?) And then SQLServer is supposed to =give an error ; as empid =3D 1 is beingreferred as foreign key in record =where empid is 2. But it doesn't happenso. SQLServer doesn't give an error. So =does that mean SQLServer firstdeletes record where empid is 2. And then =it deletes the record where empidis 1? (In short, deletes all =cascading child records first and then parentrecords) Is it SQLServer's normal and = expected behavior? Or it is notguaranteed? Or anything else? =Please guide. =Thanks.Regards,Pravin

---

--=_NextPart_000_000F_01C37454.B43BD200--|||This is a multi-part message in MIME format.
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Ok Tom. That clears the doubt. Thanks.
-- Regards,
Pravin
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:#aD7FZHdDHA.4020@.tk2msftngp13.phx.gbl...
The point I was making was that you cannot use ON DELETE CASCADE in a =self-referencing situation. You should avoid using EM for creating =tables. I stick with scripting it out and then saving the scripts under =version control.
-- Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
.
"Pravin" <expertco@.vsnl.com> wrote in message =news:usF6m5CdDHA.560@.TK2MSFTNGP11.phx.gbl...
Thanks Tom.
I got the point that because parent and child both are there in delete =statement, SQLServer doesn't throw an error.
However, another point you have specified is "In your case, you did =not specify ON DELETE CASCADE in your foreign key". Does that mean =SQLServer allows setting the option 'on delete cascade' for self =referencing keys? If yes, then please inform me how. If no, then your =answer that "In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation." is =just the answer for my question. Right?
Note: I was not able to set this option from enterprize manager. =Please guide, Thanks in Advance.
-- Regards,
Pravin Joshi
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:errsWw9cDHA.828@.TK2MSFTNGP11.phx.gbl...
In your case, you did not specify ON DELETE CASCADE in your foreign =key. If you had, it would have failed, since it is a circular =reference. In your DELETE, you specified that you were deleting both =the child and the related parent, so there would be no RI violation. If =you try the following, it should fail:
DELETE FROM emp_1 WHERE empid =3D 1
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Pravin" <expertco@.vsnl.com> wrote in message =news:OQxXPs9cDHA.456@.TK2MSFTNGP10.phx.gbl...
Let's say we have table as follows.
empid mgrid empname
--
1 null abc
2 1 pqr
empid is primary key for the table
mgrid is self referencing foreign key towards empid
Now on above table, I execute DML as:
DELETE FROM emp_1 WHERE empid in (1, 2)
So SQLSerever, will first attempt to delete record where empid is 1. =(Isn't
it?) And then SQLServer is supposed to give an error ; as empid =3D =1 is being
referred as foreign key in record where empid is 2. But it doesn't =happen
so. SQLServer doesn't give an error. So does that mean SQLServer =first
deletes record where empid is 2. And then it deletes the record =where empid
is 1? (In short, deletes all cascading child records first and then =parent
records) Is it SQLServer's normal and expected behavior? Or it is =not
guaranteed? Or anything else? Please guide. Thanks.
Regards,
Pravin
--=_NextPart_000_000D_01C374C4.4266C7F0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Ok Tom. That clears the doubt. Thanks.
-- Regards,Pravin
"Tom Moreau" = wrote in message news:#aD7FZHdDHA.4020=@.tk2msftngp13.phx.gbl...
The point I was making was that you =cannot use ON DELETE CASCADE in a self-referencing situation. You should =avoid using EM for creating tables. I stick with scripting it out and =then saving the scripts under version control.
-- Tom

----Thomas A. =Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql.
"Pravin" wrote in message news:usF6m5CdDHA.560@.T=K2MSFTNGP11.phx.gbl...
Thanks Tom.

I got the point that because parent =and child both are there in delete statement, SQLServer doesn't throw an error.

However, another point you have =specified is "In your case, you did not specify ON DELETE =CASCADE in your foreign key". Does that mean SQLServer allows setting =the option 'on delete cascade' for self referencing keys? If yes, then =please inform me how. If no, then your answer that "In =your DELETE, you specified that you were deleting both the child and the related =parent, so there would be no RI violation." is just the answer for my =question. Right?

Note: I was not able to set =this option from enterprize manager. Please guide, Thanks in Advance.
-- Regards,Pravin Joshi
"Tom Moreau" = wrote in message news:errsWw9cDHA.828@.T=K2MSFTNGP11.phx.gbl...
In your case, you did not specify =ON DELETE CASCADE in your foreign key. If you had, it would have failed, =since it is a circular reference. In your DELETE, you specified that =you were deleting both the child and the related parent, so there would =be no RI violation. If you try the following, it should =fail:

DELETE FROM emp_1 WHERE =empid =3D 1

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Pravin" wrote in =message news:OQxXPs9cDHA.456@.T=K2MSFTNGP10.phx.gbl...Let's say we have table as follows.empid mgrid =empname--1 &nbs=p; null =abc2 1 pqrempid is primary =key for the tablemgrid is self referencing foreign key towards =empidNow on above table, I execute DML as: DELETE FROM =emp_1 WHERE empid in (1, 2)So SQLSerever, will first attempt to =delete record where empid is 1. (Isn'tit?) And then SQLServer is =supposed to give an error ; as empid =3D 1 is beingreferred as foreign key =in record where empid is 2. But it doesn't happenso. SQLServer doesn't =give an error. So does that mean SQLServer firstdeletes record where =empid is 2. And then it deletes the record where empidis 1? (In short, =deletes all cascading child records first and then parentrecords) Is it =SQLServer's normal and expected behavior? Or it is notguaranteed? Or =anything else? Please guide. Thanks.Regards,Pravin

---

--=_NextPart_000_000D_01C374C4.4266C7F0--sql

Question about Primary Key

PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce

>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:

> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
>|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...[vbcol=seagreen]
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:
>

Question about Primary Key

PK columns cannot be null. But you can get uniqueness by
creating it as a unique constraint or unique index... Bruce

>--Original Message--
>I want to create a Primay Key
>as combination of 3 fields.
>Prod_NO
>Prod_Item
>Prod_cont
>In this case there is a chance that PRod_item
>or Prod_Cont will be blank, can I still create
>these three as Primary Key? If not, what is the
>alternate way to do so?
>Thank you,
>.
>
Bruce,
I think what you suggested will work, but do you foresee
any other problems in this? Will everything else
work the same? I haven't used it any times hence
the question.
Thank you,
-M
"Bruce de Freitas" wrote:

> PK columns cannot be null. But you can get uniqueness by
> creating it as a unique constraint or unique index... Bruce
>
>
|||It depends...
PK Constraints do not allow NULLs, but UNIQUE constraints allow a single
NULL.
So if your data looked like:
1, NULL, NULL
That would be okay for a UNIQUE Constraint.
Another record of
1, NULL, NULL will violate the constraint however.
HTH
Rick Sawtell
MCT, MCSD, MCDBA
"Me" <Me@.discussions.microsoft.com> wrote in message
news:33CCE845-1F74-4FE3-9570-B79E0D442C20@.microsoft.com...[vbcol=seagreen]
> Bruce,
> I think what you suggested will work, but do you foresee
> any other problems in this? Will everything else
> work the same? I haven't used it any times hence
> the question.
> Thank you,
> -M
> "Bruce de Freitas" wrote:

Question about Null value in cross tab

Dear all,
I have a question about formula in crystal report.
I have created a cross tab. In the group field, the null value will display blank.
How can it display other text if it is null instaed of blank?
Thanks alotHi,

Right Click the summary field, select "Format Field", go to "Common" Tab.

You can find X-2 for "Display" String option. Click that button and write the following formula

If IsNull(CurrentFieldValue) = True then
0.00
else
CurrentFieldValue

This will display 0 if that field contains Null and the actual value if it is not null.

Hope this will work.|||Hi
Thank you for your reply
I have modify the formula from you and place it in the right place.
However, it doesn't work.
The blank group title has not be changed to the text that I expected.
Do you have any suggestion?|||Is that field is blank or null? If it is blank, just check with blank space instead of checking null.|||I have checked both of it
but still cannot work
Here is my code
for the null value:
if IsNull({table.field}) then
"Unspecific"
else
CurrentFieldValue

for checking space
if {table.field}=" " then
"Unspecific"
else
CurrentFieldValue

both of them are shown nothing is the cross tab field
Do u have any other suggestion?
Thanks for your help!!!!|||I have an other question to ask you
I want to change the subtotal to other currency
and the exchange rate is store in other table
I don't know how to get the exchange rate for each of the countries
How can i do this by the formula?|||you are so nice and very helpful~~~|||Thanks,

For the first question, Hope you have written the formula in "Display String" (Format)section.

Try to check CurrentFieldValue instead of {table.field}.

I checked with zero, it worked.

For the second question, if you have any linking field like country code, country name use that field to link with the other table which contains the exchange rate. Write the formula by using that field.|||Thank you for reply!
I also check it with non-null value
it also works!
however, when I check it with null value, it shows nothing~
I have check it with sql statement, it is null value in the database

How come this happens??

Thanks~|||I have a new question again.

The crystal report have not show the record that is zero
How can I show the zero record??

Thanks very muchsql

Question about handling Nulls

Hi,
If I am adding 3 columns of data type real in a select clause and if any one
of those columns is a null I get a null back instead of reslult of an
addition applied to non null columns. So I used isnull(field1,0) to convert
it to zero so I get some value back instead of a null. i.e.
select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
This converts null values to zero and always get a result back even though
the result may be 0 if all values are null.
My question is what if I want to get a null back ONLY if all 3 fields have
value Null otherwise some number after applying addition.
ThanksRick,
Use a CASE expression.
Example:
select case when field1 is null and field2 is null and field3 is null then
null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
col_result From XYZ
AMB
"Rick" wrote:

> Hi,
> If I am adding 3 columns of data type real in a select clause and if any o
ne
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to conver
t
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||This isn't probably the answer you want, but something you should think
about anyway.
Why do you ever want NULL for numeric values? Does it ever make
mathematical sense? We made a decision long ago to NEVER allow nulls for
numeric values. We always populate/default our numeric columns to the value
that makes most sense. So, for example, 'price' would always start at zero
rather than null. You will most likely find that if you build in default
values for all your numerics problems such as the one you are posing just
simply go away.
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
> Hi,
> If I am adding 3 columns of data type real in a select clause and if any
> one
> of those columns is a null I get a null back instead of reslult of an
> addition applied to non null columns. So I used isnull(field1,0) to
> convert
> it to zero so I get some value back instead of a null. i.e.
> select isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) From XYZ
> This converts null values to zero and always get a result back even though
> the result may be 0 if all values are null.
> My question is what if I want to get a null back ONLY if all 3 fields have
> value Null otherwise some number after applying addition.
> Thanks
>|||Thank You Alejandro.
"Alejandro Mesa" wrote:
> Rick,
> Use a CASE expression.
> Example:
> select case when field1 is null and field2 is null and field3 is null then
> null else isnull(filed1,0)+isnull(field2,0)+isnull
(field3,0) end as
> col_result From XYZ
>
> AMB
> "Rick" wrote:
>|||Hi James
You are right. What you said makes sense in the retail business for example.
I am in the wastewater industry. I can't just create zeros and average them
for the runtime of an equipment say a motor pump flow. If I get a value Zero
from pump reading only then I will average it otherwise I have to deal with
nulls and nulls are not counted in aggregate functions.
Rick
"james" wrote:

> This isn't probably the answer you want, but something you should think
> about anyway.
> Why do you ever want NULL for numeric values? Does it ever make
> mathematical sense? We made a decision long ago to NEVER allow nulls for
> numeric values. We always populate/default our numeric columns to the val
ue
> that makes most sense. So, for example, 'price' would always start at zer
o
> rather than null. You will most likely find that if you build in default
> values for all your numerics problems such as the one you are posing just
> simply go away.
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:645FC638-123B-4E2F-8077-8A7A547EC503@.microsoft.com...
>
>|||Hmm, not sure I aggree, but then I'm not the expert. Let me try though
I have 3 pumps. Pump one pumps 100gpm, P2 50 gpm and P3 isn't turned on. I
would argue that the average still includes P3, and that is is pumping zero
gpm, not NUL gpm
I'd like to see your logic/formula - but then I probably wouldn't understand
it anyway ;-)
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||Rick,
I just posted my other reply, and then it occurred to me what the problem
might be. I bet you are using a de-normalized table. And your pumps are
columns in one table, rather than having a pumps table. That is why you
have nulls in your columns.
How off the mark am I?
JIM
"Rick" <ricky.arora@.metc.state.mn.us> wrote in message
news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
> Hi James
> You are right. What you said makes sense in the retail business for
> example.
> I am in the wastewater industry. I can't just create zeros and average
> them
> for the runtime of an equipment say a motor pump flow. If I get a value
> Zero
> from pump reading only then I will average it otherwise I have to deal
> with
> nulls and nulls are not counted in aggregate functions.
> Rick
> "james" wrote:
>|||James,
You assumed that if the pump if off the value is Zero. But thats not the
case all the time. Some times instruments read bogus value of say 2 gpm
(gallons per minute) but you don't want to count this value in an avergae
neither you want a zero.
So I am applying a range condition saying if the pump value is in between
certain range I don't want it in an average. But if it is a zer then count i
t.
Ricky
"james" wrote:

> Rick,
> I just posted my other reply, and then it occurred to me what the problem
> might be. I bet you are using a de-normalized table. And your pumps are
> columns in one table, rather than having a pumps table. That is why you
> have nulls in your columns.
> How off the mark am I?
> JIM
>
> "Rick" <ricky.arora@.metc.state.mn.us> wrote in message
> news:4D91013B-A1A4-42B9-8D89-9EF78ACA6FE9@.microsoft.com...
>
>

Question about by Import oracle data and field is null

Hi, I've a question about importing Oracle data and some fields are null. I get an error 'Conversion failed because the data value overflowed the specified type'. When i look in preview query result, via OLE db Source editor > Preview, this field contains '<value too big to display>'.

What do i do wrong? Can somebody help me?

Thanks in advance

Olaf

It may be helpful to have a few more details. Which version of SQL Server and Oracle are you using? And which method and tool are you using to import data?|||

Hello Buck,

I work with SQL server 2005 and Oracle 7.

Olaf

|||Olaf - SQL Server has several methods to export and import data, as does Oracle. For most data cleansing issues, the simplest way to eliminate data format differences is to export the data from Oracle to a text file and then import that to SQL Server. If you're looking for something that is simpler, such as using SQL Server Integration Services (SSIS) then you'll need to check the data types on both systems to ensure that they match up. You can also "push" the data from Oracle using their export tools. Whether you pull the data from Oracle using SSIS or push it from Oracle, both servers need to have the latest client drivers for each other.|||Hi Buck,
What is the problem. I have a made a SSIS, in VS2005, with an ole db source, settings to the oracle db, and an ole db destination, settings to sql 2005 db. When i look in preview

query result, via OLE db Source editor > Preview, afield

contains '<value too big to display>'. This field is a datetime oracle field and it's empty. How do i check if it's empty? And what do this field then get for an value so that i don't get problem with importing it in to SQL?

Thanks in advance

Olaf
|||

As I mentioned, you'll need the latest drivers on your SQL Server System for Oracle. Also, you can check the data you're about to import using the OPENQUERY statement in SQL Server. Look in Books Online for more information on how to use that statement.

Question : How SQL chooses an index for a process

I have just tested 3 queries using QA. The complete
test information :

--
CREATE TABLE agls1
(fyear char(4) NULL ,
fprefix char(3) NULL ,
fvcno char(20) NULL ,
fdate datetime NULL ,
fid char(15) NULL ,
fiddate datetime NULL ,
fdesc char(60) NULL ,
facc char(12) NULL ,
fval decimal(18, 2) NULL ,
fcrc char(5) NULL ,
fsub char(1) NULL ,
fmaster char(9) NULL ,
fcode char(15) NULL )
CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
fdate, fvcno)
CREATE INDEX b ON agls1 (fyear, facc, fdate,
fprefix, fvcno)
CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)

insert into agls1
( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
( '2004','A','B','123','inv','20040101','01','111' )

--query-1
select * from agls1
where fyear = '2004' and fprefix = 'inv' and
fdate = '20040101' and fvcno = '01'

--query-2
select * from agls1
where fyear = '2004' and facc = '111' and
fdate = '20040101' and fprefix = 'inv' and fvcno = '01'

--query-3
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'
--

The execution plan shows that the index a
is always used for all 3 select queries above.

I have 3 questions for you :
a. Why does SQL not choose index b for query-2 ?
Why does SQL not choose index c for query-3 ?
b. Is it right that query-2 does not benefit from
index b and query-3 does not benefit from index c ?
c. How does SQL choose an index for a process ?

Could anyone help me

Thanks in advance

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Anita (anonymous@.devdex.com) writes:
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?

There is only one row in the table, that makes the test somewhat
meaningless.

But the general is that there is always a tradeoff whether to use a
non-clustered index or not. When SQL Server finds row through a non-
clustered index, it has to go to the data page and get data requested
in the query which is not present in the index. This means that it
can be more expensive to use the index than to scan table, if the
optimizer estimates that the index will find many rows.

> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?

No, that depends on how the data looks like. Let's first take query/index
c. Say that there is over a million rows with year = 2004. In this case,
without the index, query c would have to scan all those rows in the
clustered index, whereas with the non-clustered index can find the
matching rows faster. But if there are say, 10000 rows that matches
query c, I would execpt SQL Server to use the clustered index.

As for index b, there are situations where this index could help, but
in this case, there must be many duplicates in the clustered index,
so that you actually make the query significantly more precise by adding
that extra column.

> c. How does SQL choose an index for a process ?

SQL Server uses a cost-based optimizer which makes its decisions from
statistics about the table column. Therefore the same query can get
different query plans with different data.

There is material in Books Online you can study. I can also recommend
Kalen Delaney's "Inside SQL Server 2000", which covers this topic
in detail.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

Thanks a lot for the lucid explanation.
Very helpful.

I will continue testing using minimum amount of rows
to see that SQL Server uses index c for query-3.
The data must easily force SQL Server to use
index c. If you do not mind, could you advice me
how data looks like that I should create.

Anita Hery

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||
Anita wrote:

> I have just tested 3 queries using QA. The complete
> test information :
> --
> CREATE TABLE agls1
> (fyear char(4) NULL ,
> fprefix char(3) NULL ,
> fvcno char(20) NULL ,
> fdate datetime NULL ,
> fid char(15) NULL ,
> fiddate datetime NULL ,
> fdesc char(60) NULL ,
> facc char(12) NULL ,
> fval decimal(18, 2) NULL ,
> fcrc char(5) NULL ,
> fsub char(1) NULL ,
> fmaster char(9) NULL ,
> fcode char(15) NULL )
> CREATE CLUSTERED INDEX a ON agls1 (fyear, fprefix,
> fdate, fvcno)
> CREATE INDEX b ON agls1 (fyear, facc, fdate,
> fprefix, fvcno)
> CREATE INDEX c ON agls1(fyear,fsub, fmaster, fcode)
> insert into agls1
> ( fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
> values
> ( '2004','A','B','123','inv','20040101','01','111' )
> --query-1
> select * from agls1
> where fyear = '2004' and fprefix = 'inv' and
> fdate = '20040101' and fvcno = '01'
> --query-2
> select * from agls1
> where fyear = '2004' and facc = '111' and
> fdate = '20040101' and fprefix = 'inv' and fvcno = '01'
> --query-3
> select * from agls1
> where fyear = '2004' and fsub = 'A' and fmaster = 'B'
> and fcode = '123'
> --
> The execution plan shows that the index a
> is always used for all 3 select queries above.
> I have 3 questions for you :
> a. Why does SQL not choose index b for query-2 ?
> Why does SQL not choose index c for query-3 ?
> b. Is it right that query-2 does not benefit from
> index b and query-3 does not benefit from index c ?
> c. How does SQL choose an index for a process ?

The last question in particular has lots of book chapters on it.
The fact is that the table is so small that no index can really
help much. A blind table-scan is fastest with a one-row table.
To see more intuitive index use, you should probably test with
a table having thousands of well-distributed rows.
Joe Weinstein at BEA
> Could anyone help me
> Thanks in advance
> Anita Hery
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Anita (anonymous@.devdex.com) writes:
> I will continue testing using minimum amount of rows
> to see that SQL Server uses index c for query-3.
> The data must easily force SQL Server to use
> index c. If you do not mind, could you advice me
> how data looks like that I should create.

You can always use an index hint to convince SQL Server to use an index:

SELECT * FROM tbl WITH (INDEX = c)

To make it simple you should have fyear = 2004 in all rows you create.
But the values in fsub, fmastser and fcode should vary.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns949BF26CFB825Yazorman@.127.0.0.1...
> Anita (anonymous@.devdex.com) writes:
> > I will continue testing using minimum amount of rows
> > to see that SQL Server uses index c for query-3.
> > The data must easily force SQL Server to use
> > index c. If you do not mind, could you advice me
> > how data looks like that I should create.
> You can always use an index hint to convince SQL Server to use an index:
> SELECT * FROM tbl WITH (INDEX = c)

Just to jump in, you can of course do that. However (and this is to Anita,
not Erland since I know he's aware of this), it's generally a fairly bad
idea to force an index hint, since your data may later change in such a way
to make the index less useful.

I'd recommend finding some of the papers Kalen Delany has written on this
subject as it may help.

> To make it simple you should have fyear = 2004 in all rows you create.
> But the values in fsub, fmastser and fcode should vary.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I have inserted 810 rows by following Erland's advice.
With these rows, SQL Server uses index c when executes
query :
select * from agls1
where fyear = '2004' and fsub = 'A' and fmaster = 'B'
and fcode = '123'

Thanks again to all of you that sent the replies

Anita Hery

Note :
Below is my insert test :

declare @.sub as int, @.master as int, @.code as int
set @.sub = 0 --max 15
set @.master = 0 --max 6
set @.code = 0 --max 15 digit

lsub:
set @.sub = @.sub + 1
set @.master = 0
set @.code = 0
lmaster:
set @.master = @.master + 1
set @.code = 0
lcode:
set @.code = @.code + 1
insert into agls1
(fyear,fsub,fmaster,fcode,fprefix,fdate,fvcno,facc )
values
('2004',str(@.sub,1),str(@.master,9),
str(@.code,15),'inv','20040101','01','111')

if @.code < 15 goto lcode
if @.master < 6 goto lmaster
if @.sub < 9 goto lsub

select * from agls1
where fyear = '2004' and fsub = 'A' and
fmaster = 'B' and fcode = '123'

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!