Identity column: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

If numeric query

I have a table with a memo field that sometimes contains an account
number at the same location. I need to find a record related to that
account in another table. I can write and run a query for a record
which works fine if the account number exists, but (obviously) get an
error if it does not. I wrote the following query which I don't
understand why it will not work.
IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1)
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
END
ELSE
BEGIN
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate
FROM tblRegister
END
Any help on this would be greatly appreciated.
Thanks
CharlesA couple of comments.
First, isnumeric isn't foolproof. You may wish to review this source:
isnumeric -What is wrong?
http://www.aspfaq.com/show.asp?id=2390
Second, it appears that you are attempting to compare a string to a number,
e.g.,
SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the compari
son. And this will not use indexing, so expect performance to be poor.
If you were to add another column to the Register table, set its value to be
the substring of the Memo field (or better yet, have the application provid
e the number), and then index that column, performance would be rather sprig
htly.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
<cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegrou
ps.com...
>I have a table with a memo field that sometimes contains an account
> number at the same location. I need to find a record related to that
> account in another table. I can write and run a query for a record
> which works fine if the account number exists, but (obviously) get an
> error if it does not. I wrote the following query which I don't
> understand why it will not work.
>
> IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1)
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate, fldNote
> FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> END
>
> ELSE
>
> BEGIN
> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> fldStatus, fldLogDate
> FROM tblRegister
> END
>
> Any help on this would be greatly appreciated.
> Thanks
> Charles
>|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number
, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the compa
rison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to
be the substring of the Memo field (or better yet, have the application prov
ide the number), and then index that column, performance would be rather spr
ightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g
2000cwa.googlegroups.com...
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
> <META content="MSHTML 6.00.5296.0" name=GENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=Arial size=2>A couple of comments.</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>First, isnumeric isn't foolproof. You may wis
h to
> review this source:</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes
">isnumeric
> -What is wrong?<BR></SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><A
> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=
2390</A>
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face=Arial size=2></FONT></SPAN> </P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
> </SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s"><FONT
> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) =
> tblNotes.fldMemberNo</FONT></SPAN></P>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)<BR></P></SPAN>
> <P class=MsoNormal
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">You
> need to cast the first part as a numeric value in order make the compariso
n.
> </SPAN><SPAN
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: ye
s">And
> this will not use indexing, so expect performance to be poor.</P></SPAN></
DIV>
> <DIV><FONT face=Arial size=2>If you were to add another column to the Regi
ster
> table, set its value to be the substring of the Memo field (or better yet,
have
> the application provide the number), and then index that column, performan
ce
> would be rather sprightly.</FONT><BR><FONT face=Arial size=2>-- <BR>Arnie
> Rowland, Ph.D.<BR>Westwood Consulting, Inc</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>Most good judgment comes from experience. <BR
>Most
> experience comes from bad judgment. <BR>- Anonymous</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2>You can't help someone get up a hill without
> getting a little closer to the top yourself.<BR>- H. Norman
> Schwarzkopf</FONT></DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2></FONT> </DIV>
> <DIV><FONT face=Arial size=2><</FONT><A
> href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"><FONT face=Arial
> size=2>cbanks@.bjtsupport.com</FONT></A><FONT face=Arial size=2>> wrote
in
> message </FONT><A
> href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"><FONT
> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com</FONT></A
><FONT
> face=Arial size=2>...</FONT></DIV><FONT face=Arial size=2>>I have a tab
le
> with a memo field that sometimes contains an account<BR>> number at the
same
> location. I need to find a record related to that<BR>> accoun
t in
> another table. I can write and run a query for a record<BR>>
which
> works fine if the account number exists, but (obviously) get an<BR>> er
ror if
> it does not. I wrote the following query which I don't<BR>> u
nderstand
> why it will not work.<BR>> <BR>> IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING<BR>> (tblRegister.fldMemo,6,9)) =
> 1)<BR>> <BR>> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMe
mo,
> fldAmt, fldTransmitted,<BR>> fldStatus, fldLogDate, fldNote<BR>> FRO
M
> tblRegister INNER JOIN tblNotes ON SUBSTRING<BR>> (tblRegister.fldMemo,
6,9) =
> tblNotes.fldMemberNo<BR>> END<BR>> <BR>> ELSE<BR>> <BR>>
> BEGIN<BR>> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,<BR>> fldStatus, fldLogDate<BR>> FROM tblRegister<BR>
> END<BR>> <BR>> Any help on this would be greatly appreciated.<BR>>
;
> Thanks<BR>> Charles<BR>></FONT></BODY></HTML>
> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof

.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
which made the statement invalid. The statement 'should' work as:
IF EXISTS
( SELECT fldMemo
FROM tblRegister
WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message news:uFkIfWP
$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>
> I'm not sure why this is better than
>
> IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
>
> ? The exists is potentially faster, and certainly not any slower, than a
> select count.
>
> The syntax problem he had was likely just a missing parenthesis...
>
> A
>
>|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]">
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If numeric query

A couple of comments.
First, isnumeric isn't foolproof. You =may wish to review this source:
isnumeric -What is wrong?http://www.aspfaq.com/show.asp?id=3D2390">http://www.aspfaq.com/s=how.asp?id=3D2390
Second, it appears that you are attempting to compare a string to a number, =e.g., SUBSTRING(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo
(I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
You need to cast the first part as a numeric value in order make the =comparison. And this will not use indexing, so expect performance to be =poor.
If you were to add another column to =the Register table, set its value to be the substring of the Memo field (or better =yet, have the application provide the number), and then index that column, =performance would be rather sprightly.-- =Arnie Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com=...>I =have a table with a memo field that sometimes contains an account> number at =the same location. I need to find a record related to that> account =in another table. I can write and run a query for a record> =which works fine if the account number exists, but (obviously) get an> =error if it does not. I wrote the following query which I don't> =understand why it will not work.> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) ==3D 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, =fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> =FROM tblRegister INNER JOIN tblNotes ON SUBSTRING> =(tblRegister.fldMemo,6,9) =3D tblNotes.fldMemberNo> END> > ELSE> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,> fldStatus, fldLogDate> FROM =tblRegister> END> > Any help on this would be greatly =appreciated.> Thanks> Charles>

--=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Arnie,
Thanks for the reply. My main issue is when I try to run this as an IF
statement I get an error "Incorrect syntax near the keyword 'BEGIN'."
I actually tried it without the 'Exists' after IF. If I run
SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
fldStatus, fldLogDate, fldNote
FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo with a where clause to
locate a record with a valid member number, it works fine. My issue is
running this when I don't have a valid member ID, which is what I am
trying to accomplish with the if statement.
It would have been better to include the member number in my register
table, but this was not in the original scope and I really don't want
to modify my table structure.
Charles
Arnie Rowland wrote:
> A couple of comments.
> First, isnumeric isn't foolproof. You may wish to review this source:
> isnumeric -What is wrong?
> http://www.aspfaq.com/show.asp?id=2390
>
> Second, it appears that you are attempting to compare a string to a number, e.g.,
> SUBSTRING(tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> (I'm assuming that tblNotes.fldMemberNo is a numeric datatype.)
>
> You need to cast the first part as a numeric value in order make the comparison. And this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register table, set its value to be the substring of the Memo field (or better yet, have the application provide the number), and then index that column, performance would be rather sprightly.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> <cbanks@.bjtsupport.com> wrote in message news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com...
> >I have a table with a memo field that sometimes contains an account
> > number at the same location. I need to find a record related to that
> > account in another table. I can write and run a query for a record
> > which works fine if the account number exists, but (obviously) get an
> > error if it does not. I wrote the following query which I don't
> > understand why it will not work.
> >
> > IF EXISTS (SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> > (tblRegister.fldMemo,6,9)) = 1)
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate, fldNote
> > FROM tblRegister INNER JOIN tblNotes ON SUBSTRING
> > (tblRegister.fldMemo,6,9) = tblNotes.fldMemberNo
> > END
> >
> > ELSE
> >
> > BEGIN
> > SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt, fldTransmitted,
> > fldStatus, fldLogDate
> > FROM tblRegister
> > END
> >
> > Any help on this would be greatly appreciated.
> > Thanks
> > Charles
> >
> --=_NextPart_000_048F_01C6FC3D.3D5AC000
> Content-Type: text/html; charset=iso-8859-1
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 5062
> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &

>
>
>
>
>
> A couple of comments.
>
> First, isnumeric isn't foolproof. You may wish to
> review this source:
>
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial; mso-no-proof: yes">isnumeric
> -What is wrong? style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> href="http://links.10026.com/?link=http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390">http://www.aspfaq.com/show.asp?id=2390
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face=Arial size=2>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">Second,
> it appears that you are attempting to compare a string to a number, e.g.,
>
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes"> face="Courier New">SUBSTRING(tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">(I'm assuming
> that tblNotes.fldMemberNo is a numeric datatype.)
> style="MARGIN: 0in 0in 6pt; mso-layout-grid-align: none"> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">You
> need to cast the first part as a numeric value in order make the comparison.
> style="FONT-SIZE: 10pt; COLOR: black; FONT-FAMILY: Arial; mso-no-proof: yes">And
> this will not use indexing, so expect performance to be poor.
> If you were to add another column to the Register
> table, set its value to be the substring of the Memo field (or better yet, have
> the application provide the number), and then index that column, performance
> would be rather sprightly.-- Arnie
> Rowland, Ph.D.Westwood Consulting, Inc
>
> Most good judgment comes from experience. Most
> experience comes from bad judgment. - Anonymous
>
> You can't help someone get up a hill without
> getting a little closer to the top yourself.- H. Norman
> Schwarzkopf
>
>
>< href="http://links.10026.com/?link=mailto:cbanks@.bjtsupport.com"> size=2>cbanks@.bjtsupport.com> wrote in
> message href="http://links.10026.com/?link=news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com"> face=Arial
> size=2>news:1162249400.557355.12560@.k70g2000cwa.googlegroups.com face=Arial size=2>...>I have a table
> with a memo field that sometimes contains an account> number at the same
> location. I need to find a record related to that> account in
> another table. I can write and run a query for a record> which
> works fine if the account number exists, but (obviously) get an> error if
> it does not. I wrote the following query which I don't> understand
> why it will not work.> > IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING> (tblRegister.fldMemo,6,9)) => 1)> > BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo,
> fldAmt, fldTransmitted,> fldStatus, fldLogDate, fldNote> FROM
> tblRegister INNER JOIN tblNotes ON SUBSTRING> (tblRegister.fldMemo,6,9) => tblNotes.fldMemberNo> END> > ELSE> >
> BEGIN> SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,> fldStatus, fldLogDate> FROM tblRegister>
> END> > Any help on this would be greatly appreciated.>
> Thanks> Charles>

> --=_NextPart_000_048F_01C6FC3D.3D5AC000--|||Charles,
exists is really for subqueries. However you could use:
IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1) > 0
....
or
SELECT fldMemo FROM tblRegister WHERE ISNUMERIC(SUBSTRING
(tblRegister.fldMemo,6,9)) = 1
if @.@.rowcount > 0
....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .|||> exists is really for subqueries. However you could use:
> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING
> (tblRegister.fldMemo,6,9)) = 1) > 0
I'm not sure why this is better than
IF EXISTS (SELECT 1 FROM tblRegister ...)
BEGIN
END
? The exists is potentially faster, and certainly not any slower, than a
select count.
The syntax problem he had was likely just a missing parenthesis...
A|||Thanks Aaron - my goof :(.
For some obscure reason I had assumed that it was returning a single row
(long night and needed more coffee!), but as I look more closely at what I
wrote I see issues with both solutions - Charles please disregard my earlier
post.
Regards,
Paul Ibison|||This is a multi-part message in MIME format.
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Actually, not a missing parens, but including an equality [ =3D 1 ) > 0 =] which made the statement invalid. The statement 'should' work as:
IF EXISTS ( SELECT fldMemo FROM tblRegister WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
)
BEGIN
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in =message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...
>> exists is really for subqueries. However you could use:
>> IF (SELECT count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING >> (tblRegister.fldMemo,6,9)) =3D 1) > 0
> > I'm not sure why this is better than
> > IF EXISTS (SELECT 1 FROM tblRegister ...)
> BEGIN
> END
> > ? The exists is potentially faster, and certainly not any slower, =than a > select count.
> > The syntax problem he had was likely just a missing parenthesis...
> > A > >
--=_NextPart_000_057B_01C6FCC2.47296CF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Actually, not a missing parens, but =including an equality [ =3D 1 ) > 0 ] which made the statement invalid. The =statement 'should' work as:
IF EXISTS
( SELECT =fldMemo =FROM tblRegister
=WHERE isnumeric( substring( tblRegister.fldMemo, 6, 9 ))
=)
=BEGIN-- Arnie =Rowland, Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Aaron Bertrand [SQL Server MVP]" = wrote in message news:uFkIfWP$GHA.4708@.TK2MSFTNGP05.phx.gbl...> exists is really for subqueries. However you could use:> IF (SELECT =count(*) FROM tblRegister WHERE ISNUMERIC(SUBSTRING > (tblRegister.fldMemo,6,9)) =3D 1) > 0> > I'm not sure =why this is better than> > IF EXISTS (SELECT 1 FROM tblRegister =...)> BEGIN> END> > ? The exists is potentially =faster, and certainly not any slower, than a > select count.> > =The syntax problem he had was likely just a missing parenthesis...> => A > >

--=_NextPart_000_057B_01C6FCC2.47296CF0--|||> Actually, not a missing parens, but including an equality [ = 1 ) > 0 ]
> which made the statement invalid.
Where? I didn't see his code, I just saw fragments of it.
A

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
Regards,
Jamie
On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>
|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.
|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
[vbcol=seagreen]
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:

>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching th
e
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when i
t
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:

> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:

>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 o
r
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching t
he
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when
it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially doe
s
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does i
t
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that ther
e
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:

>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
(snip)[vbcol=seagreen]
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

If Not Exists?

Curious about the above:
If there is a query that says
If not exists(select * from table)
versus one that says
If not exists(select * from table with(NoLOCK))
Is the second statement more correct than the first (considering that there
may be hundreds of hits to that table in a very short space of time -
possible 100's per second)?
Or is the second statement truly no different than the first as the
statement would discover that the record had a count > 0 and thus validate
the "if not exists" statment as false?
--
Regards,
JamieOn Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
>Curious about the above:
>If there is a query that says
>If not exists(select * from table)
>versus one that says
>If not exists(select * from table with(NoLOCK))
>Is the second statement more correct than the first (considering that there
>may be hundreds of hits to that table in a very short space of time -
>possible 100's per second)?
Hi thejamie,
The seconds statement is faster, but LESS correct. If the table has no
rows, one user proceeds to insert a row that violates a constraint (so
that the insert will be rolled back) and another user runs the second
version of the query, it will "see" the row inserted by the first user,
even though that row logically never existed. And the revere goes to: if
one user runs a DELETE without WHERE clause that gets rolled back later,
the query with nolock hint will temporarily see no rows, even though
they logically were never gone.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
three million of these a week - it is hard to touch this table without
conflicts - we sort of gave up and now use the with(nolock) when touching the
table. The issue came up as this particular call to the transactions table
brought up the question of what does the "EXISTS" keyword actually do when it
accesses a table.
For example, does it open the PK index to see if it has a count and shut it
back down again?
or possibly:
Opens the table like a select top 1 call might do,
or worst possibly scenario - "EXISTS" opens the entire index and scans
through from beginning to end to determine the condition as true or false.
What is actually written is " IF NOT EXISTS " which flowed more logically -
from the context - if the table does not exist - which at about 2:30 AM in
the morning when it gets archived to the datawarehouse - it essentially does
not exist from that point until about 5 am when the warehouse starts up -
question remains:
Is the table accessing all the records with the "Exists" keyword, or does it
try to scan the entire index... etc?
--
Regards,
Jamie
"Hugo Kornelis" wrote:
> On Mon, 30 Jul 2007 11:02:05 -0700, thejamie wrote:
> >Curious about the above:
> >
> >If there is a query that says
> >If not exists(select * from table)
> >versus one that says
> >If not exists(select * from table with(NoLOCK))
> >
> >Is the second statement more correct than the first (considering that there
> >may be hundreds of hits to that table in a very short space of time -
> >possible 100's per second)?
> Hi thejamie,
> The seconds statement is faster, but LESS correct. If the table has no
> rows, one user proceeds to insert a row that violates a constraint (so
> that the insert will be rolled back) and another user runs the second
> version of the query, it will "see" the row inserted by the first user,
> even though that row logically never existed. And the revere goes to: if
> one user runs a DELETE without WHERE clause that gets rolled back later,
> the query with nolock hint will temporarily see no rows, even though
> they logically were never gone.
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
>|||On Tue, 31 Jul 2007 06:02:05 -0700, thejamie wrote:
>Yes. LESS correct, unfortunately it is the transaction table - roughly 2 or
>three million of these a week - it is hard to touch this table without
>conflicts - we sort of gave up and now use the with(nolock) when touching the
>table. The issue came up as this particular call to the transactions table
>brought up the question of what does the "EXISTS" keyword actually do when it
>accesses a table.
>For example, does it open the PK index to see if it has a count and shut it
>back down again?
>or possibly:
>Opens the table like a select top 1 call might do,
>or worst possibly scenario - "EXISTS" opens the entire index and scans
>through from beginning to end to determine the condition as true or false.
>What is actually written is " IF NOT EXISTS " which flowed more logically -
>from the context - if the table does not exist - which at about 2:30 AM in
>the morning when it gets archived to the datawarehouse - it essentially does
>not exist from that point until about 5 am when the warehouse starts up -
>question remains:
>Is the table accessing all the records with the "Exists" keyword, or does it
>try to scan the entire index... etc?
Hi thejamie,
Both EXISTS and NOT EXISTS will stop processing as soon as the first row
matching the WHERE clause (if any) is found.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
wrote:
> Curious about the above:
> If there is a query that says
> If not exists(select * from table)
> versus one that says
> If not exists(select * from table with(NoLOCK))
> Is the second statement more correct than the first (considering that there
> may be hundreds of hits to that table in a very short space of time -
> possible 100's per second)?
> Or is the second statement truly no different than the first as the
> statement would discover that the record had a count > 0 and thus validate
> the "if not exists" statment as false?
> --
> Regards,
> Jamie
Suggestion:
Try using Select 1 instead of Select * in this scenario. That is an
old db tuning trick. Let's it use whatever index fits the where
clause and doesn't have to go to the table, retrieve and subsequently
discard all of the data in the found rows.|||On Wed, 01 Aug 2007 17:12:50 -0700, karlag92 wrote:
>On Jul 30, 1:02 pm, thejamie <theja...@.discussions.microsoft.com>
>wrote:
>> Curious about the above:
>> If there is a query that says
>> If not exists(select * from table)
>> versus one that says
>> If not exists(select * from table with(NoLOCK))
(snip)
>Suggestion:
>Try using Select 1 instead of Select * in this scenario. That is an
>old db tuning trick.
Hi karlag92,
The trick is, in fact, so old that it doesn't apply anymore. And it
actually never has applied for SQL Server. It was, IIRC, either Oracle
or DB2 that erroneously decided to expand the * to a column list even if
it was part of a [NOT] EXISTS subquery - and that has of course long be
fixed.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Wednesday, March 28, 2012

IF EXISTS statement in my perl program

I am having trouble finishing my query.

This is what I have:

IF EXISTS(Select ApplicationID from Application Where Application = '&_')
Insert Into PCApp(ApplicationID, SystemNetName)
Values( , $HoH->{Host}{SystemNetName})

I am not sure what to put in the blank within the Values parenthesis. I need to obtain the ApplicationID that is checked in the IF EXISTS section. But I cannot put a select statement into the Values() section.

Any suggestions would be appreciated.

Thanks,
LauraUse a SELECT statement instead of the VALUES clause.

-PatP|||And using a select statement will still insert the values into the table?|||In an INSERT statement, you can use the VALUES clause for a list of constants, or a SELECT clause for a list of expressions. The SELECT can include multiple rows and/or contain UNION operators to create multiple row inserts using just one INSERT statement. The SELECT buys you the ability to use expressions (including function calls), generate multiple rows, etc.

-PatP|||Ok I think I got it. Off the subject, can the IF...ELSE Contain an embedded IF...ELSE? Is it ok to have two inserts with the if section?

For example,

IF NOT EXISTS (Select ApplicationID from Application Where (Application = '$_' ))
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')

ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_' AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";

Thanks for your help.
-Laura|||I think what you meant was:IF NOT EXISTS (Select ApplicationID
FROM Application Where (Application = '$_' ))
BEGIN
INSERT INTO Application(Application)
Values('$_')

INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')
END
ELSE
INSERT INTO PCApp(ApplicationID, SystemNetName)
SELECT Application.ApplicationID, Host.SystemNetName
FROM Application CROSS JOIN Host
WHERE (Application.Application = '$_'
AND Host.SystemNetName = '$HoH->{Host}{SystemNetName}')";Note the addition of the BEGIN...END (in red) to your code.

-PatP|||CROSS JOIN?

And what's with the double quote on the end?|||Ok, so the cross join isn't the way that I'd approach it, but it would work... Kind of like the way you had to construct joins using the pre-SQL-89 syntax. Ugly, but adequate to the job!

The double quote hanging off of the end is actually because Laura is taking this SQL out of the middle of her Perl code. It isn't really part of the SQL syntax at all.

-PatP|||Really...OK

Laura...start writting stored procedures and execute them instead....

Never did learn Pearl...though we did use it for an Oracle project once...

I gotta find a Rexx interpreter for Windoze....|||Originally, I used a left join but when I tested my query in sql server's enterprise manager it automatically changed it to cross join.

The begin and end worked. Thanks for the help.

-Laura|||I gotta find a Rexx interpreter for Windoze....I've never tried it, but I've heard that Reginald (http://www.borg.com/~jglatt/rexx/win32/rxusrw32.htm) isn't too bad.

-PatP|||Yeah, I am pretty new at SQL Server. Just started learning it last month because I am going to be the database administrator. So, I have not begun using stored procedures yet, but I will.

Thanks,
Laura|||Hold the phone...Enterprise Manager?

Do you mean Query Analyzer?

And Pat....RxSocks....Not that I'll find a practical application for it...(Well maybe I could replace DTS), but I bet I can get it to talk to SQL Server...

Very cool

Thanks|||Enterprise Manager -> Opened the table view to see the records that were stored -> on the top toolbar there is an sql button that I tested my statements. It probably isn't the best way, but I wanted to have quick access to my table design and query results.

I am aware of Query Analyzer but I have not used it much.

-Laurasql

IF EXISTS SQL Question - using Query Analyzer

I want to only go after Distinct email addressess that contain an @. symbol.

But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?

IFEXISTS (SELECT DISTINCT user_usernameFROM usrWHERE(user_usernameLIKE N'%@.%'))SELECT *FROM usrOrder By user_username

Try something like:

IFEXISTS (SELECT DISTINCT user_usernameFROM usrWHERE(user_usernameLIKE N'%@.%'))
BEGIN


SELECT *
FROM usrOrder By user_username

END

|||

IF EXISTS only checks for existence of a record matching the WHERE condition. The SELECT is never evaluated. So DISTINCT in SELECT doesnt help. you might as well use SELECT * or SELECT 1. As soon as it finds at least one record that has an "@." in the User_Username the condition evaluates to TRUE.

If Exists capture value returned from stored proc

I have a stored proc with a query which checks whether an identicalvalue is already in the database table. If so, it returns a value of 1.How do I caputure this value in an asp.net page in order to display amessage accordingly? (using ASP.NET 1.1)

Currently my stored proc looks something like this (snippet only):
If Exists(
SELECT mydoc WHERE...
)
Return 1
Else
...INSERT INTO... code here.Did this exact thing for someone already in the past 2 weeks, search the forums.|||

Motley wrote:
Did this exact thing for someone already in the past 2 weeks, search the forums.

Thanks, will do so.sql

If Else question URGENT

I want to run a query that will insert rows into another table.

I also want to do some calculations on a couple of the columns:

SELECT
KEYCODESTRINGDESCRIPTION,
STRING,
Mailed,
Sales,
Orders,
CATALOGTITLE,
Response = Orders / Mailed,
[Average Invoice] = Sales / Orders,
SMP = (Sales / Mailed) * 1000

INTO TP_GA_REPORT
FROM TP_GA_REPORT_TEMP

I have a condition where some of the colums might have a 0 in them, which of course causes a "Divide by 0" error. What I would like to do is put an IF statement in the query to deal with this 0.

i.e.

if orders = 0 then response=0
else response = Response = Orders / Mailed

Hope this makes sense!

Thanks KenTry using SQL CASE:

SELECT ...,
CASE WHEN Mailed = 0 THEN 0 ELSE Orders / Mailed END,
...
FROM ...|||Thank you!!! So very much, worked like a charm!

If Else query for a complex data set

I am creating an application which uses logic similar to J.D.Edwards (for those of you are familiar with its wildcarding data structure).

Basically, a customer purchases a particular house, with a particular elevation, in a particular community. This data is stored in a Customer table. From the customer's selection (community, plan, elevation) criteria, I need to create a unique list of options from data stored in an optionmaster table.

A customer's data looks similar to this in the customer table:

CustomerID

CommunityID

Community Name

PlanID

Plan Name

ElevationID

ElevationName

1234567

Hickory Hills

Allen

The Optionmaster table is structured like this (there are actually about 1000 records):

Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price

4567

Optional Window

250

1234

Optional Door

100

1234

Optional Door

Hickory Hills

4567

Optional Brick

Allen

250

9101

Optional Dormer

Allen

9125

Optional Tub

Smithville

800

9125

Optional Kitchen

Lori

2500

First. Based on the customer's table I need to first select all options = to their community, as well as those available to communities everywhere (+). In some cases the same option is maintained for both at both levels. In that case the community specific option must be selected.

To do this I think I need an if else statement to select those records:

In this case, if CommID =7, select record, else select 0. This eliminates the other Smithville community and pulls the correct option # 1234.

The result would look like this:

Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price

4567

Optional Window

250

1234

Optional Door

Hickory Hills

4567

Optional Brick

Allen

250

9101

Optinal Dormer

Allen

9125

Optinal Kitchen

Lori

2500

Second, from this dataset, I need to select all options equal to the plan ID, and those which apply to all plans (+). So, the Lori Plan data goes away.

Something like, If the plan ID = 25, select record, else 0. the result would be:

Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price

4567

Optional Window

250

1234

Optional Door

Hickory Hills

4567

Optional Brick

Allen

250

9101

Optinal Dormer

Allen

Third and finally, based on that dataset, I would have to select any elevation specific options for that plan or options for all elevations of that plan (+).

Something like this: If the Elevation ID = 2, select record, else. 0. Note the elevation B data goes away.

The final result would be for this customer is:

Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price

4567

Optional Window

250

1234

Optional Door

Hickory Hills

4567

Optional Brick

Allen

250

Any assistance anyone can provide in coming up with a sql statement to do this would be appreciated.

Something like, If the plan ID = 25, select record, else 0. the result would be:

Option #

Option Name

CommID

Community Name

PlanID

Plan Name

ElevID

Elevation Name

Price

4567

Optional Window

250

1234

Optional Door

Hickory Hills

4567

Optional Brick

Allen

250

9101

Optinal Dormer

Allen

why did ooption #4567 stay here. the rest i follow.

|||

4567 Stays because it is available in all communites (signified by the +). At the community level, a customer can choose options available (+) AND CommID 7 "Hickory Hills" options. In situations were there is are two options, as in 1234, the community specific one is chosen.

This same pattern goes on 2 more times.

Basically, it starts general, and gets specific.

|||Just one suggestion:?there?is?no?PK?in the Optionmaster table, which is useful for filtering rows in that table.?Not?a?good?table?desing. For performance consideration, I also suggest you add a PK:
ALTER TABLE OptionMaster ADD UniqueID INT PRIMARY KEY IDENTITY(1,1)

And for your last step, I can't understand why 9101 goes away, as this row has ElevationID=2, which should be returned according to your rule. Anyways, try some query as following:

DECLARE @.PlanID int,@.ElevationID int
SELECT @.PlanID=25,@.ElevationID=2

SELECT o.*
FROM Optionmaster o ,Customers c
WHERE
(o.CommID=c.CommunityID
OR (o.CommID=0
AND NOT EXISTS (SELECT 1 FROM Optionmaster
WHERE Option#=o.Option#
AND CommID=c.CommunityID)))
AND (o.PlanID=@.PlanID OR (o.PlanID=0 AND o.PlanName ='+'))
AND (o.ElevationID=@.ElevationID OR (o.ElevationID=0 AND o.ElevationName='+'))|||

Thanks for the reply. There is actually a PK but I didn't show it.

Actually, I solved it a few minutes ago.

What I found out was the actual JD Edwards Option Master table has a column with a value (1-9 -- 1 being most specific, 9 being less specific). So all options are stored in 1 of 9 ways: (this is how it can be structured in their system).

LevelCommunityPhasePlanElevation1BED0000TwoShiloA2BED0000TwoShilo+3BED0000Two++4BED0000+ShiloA5BED0000+Shilo+6BED0000+++7++ShiloA8++Shilo+9++++

So, basically, I when I run a query on all the following data:

LevelCommunityPhasePlanElevationOption#DescriptionPrice1BED0000TwoShiloA20043 Car Garage1251HTR0000TwoShiloA20043 Car Garage1262BED0000TwoShilo+20043 Car Garage2502HTR0000TwoShilo+20043 Car Garage2513BED0000Two+ + 20043 Car Garage5003HTR0000Two+ + 20043 Car Garage5014BED0000+ ShiloA20043 Car Garage2504HTR0000+ ShiloA20043 Car Garage2515BED0000+ Shilo+ 20043 Car Garage5005HTR0000+ Shilo+ 20043 Car Garage5015BED0000+ Shilo+ 475530 Year Shingles05HTR0000+ Shilo+ 475530 Year Shingles8506BED0000+ + + 20043 Car Garage10006HTR0000+ + + 20043 Car Garage10016HTR0000+ + + 2550Crown Molding17+ + ShiloA20043 Car Garage5007+ + ShiloA20043 Car Garage5018+ + Shilo+ 20043 Car Garage10008+ + Shilo+ 20043 Car Garage10019+ + ++ 20043 Car Garage20009+ + ++ 20043 Car Garage20019++++2550Crown Molding09++++1295bonus room3000

Where the community = "Bed0000', Phase = "Two", Plan = "Shilo" and Elevation ="A", I get 4 records

LevelCommunityPhasePlanElevationOption#DescriptionPrice1BED0000TwoShiloA20043 Car Garage1255BED0000+ Shilo+ 475530 Year Shingles09++++2550Crown Molding09++++1295bonus room3000

I created a demo form to input the parameters. Then, I created a query to first select the relevant option data according to JDE's 9 levels. This gave me all the possible records. Then, I filtered the results based on the MIN value of Level (grouped on the option #). This gave me the specific records.

There's probably a better way of doing this, but I can't figure one out. I'd like to have it as a stored procedure but whenever I tried I kept running into problems declaring the variables. I don't know how much that will impact performance. I know it would be better to run on the server...I'll figure it out eventually, I'm still a bit new to SQL2005.

Here's what it looks like.

<%@. Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb"Inherits="_Default" %><!DOCTYPE html PUBLIC"-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:SqlDataSource ID="DSSpecificOptionData" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT F44H401.ocoplvl, F44H401.ochbmcus, F44H401.occphase, F44H401.ochbplan, F44H401.ochbelev, F44H401.ocooption, F44H401.ocdlo1, F44H401.occspr FROM F44H401 INNER JOIN [Find_Lowest_Level] ON F44H401.ocoplvl = [Find_Lowest_Level].[Level] AND F44H401.ocooption = [Find_Lowest_Level].ocooption WHERE (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev ='+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = @.occphase) OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = @.ochbmcus) AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = @.ochbelev) AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = @.ochbplan) AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+') OR (F44H401.ochbmcus = N'+') AND (F44H401.ochbplan = N'+') AND (F44H401.ochbelev = N'+') AND (F44H401.occphase = N'+')"> <SelectParameters> <asp:ControlParameter ControlID="TBComm" Name="ochbmcus" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPlan" Name="ochbplan" PropertyName="Text" /> <asp:ControlParameter ControlID="TBElev" Name="ochbelev" PropertyName="Text" /> <asp:ControlParameter ControlID="TBPhas" Name="occphase" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource> <br /> <table style="position: static"> <tr> <td style="width: 100px"> Community "bed0000"</td> <td style="width: 100px"> <asp:TextBox ID="TBComm" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Phase "two"</td> <td style="width: 100px"> <asp:TextBox ID="TBPhas" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Plan "shilo"</td> <td style="width: 100px"> <asp:TextBox ID="TBPlan" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> Elev "a"</td> <td style="width: 100px"> <asp:TextBox ID="TBElev" runat="server" Style="position: static"></asp:TextBox></td> </tr> <tr> <td style="width: 100px"> <asp:Button ID="Button1" runat="server" Style="position: static" Text="Search" /></td> <td style="width: 100px"> </td> </tr> </table>   <br /> <span style="color: #0000ff">Results with most specific records:</span><br /> <asp:DataList ID="DataList1" runat="server" DataSourceID="DSSpecificOptionData" Style="position: static"> <ItemTemplate> <table style="position: static"> <tr> <td style="width: 100px"> <asp:Label ID="ocoplvlLabel" runat="server" Style="position: static" Text='<%# Eval("ocoplvl") %>' Width="19px"></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbmcusLabel" runat="server" Style="position: static" Text='<%# Eval("ochbmcus") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occphaseLabel" runat="server" Style="position: static" Text='<%# Eval("occphase") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbplanLabel" runat="server" Style="position: static" Text='<%# Eval("ochbplan") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ochbelevLabel" runat="server" Style="position: static" Text='<%# Eval("ochbelev") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocooptionLabel" runat="server" Style="position: static" Text='<%# Eval("ocooption") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="ocdlo1Label" runat="server" Style="position: static" Text='<%# Eval("ocdlo1") %>'></asp:Label></td> <td style="width: 100px"> <asp:Label ID="occsprLabel" runat="server" Style="position: static" Text='<%# Eval("occspr") %>'></asp:Label></td> </tr> </table> </ItemTemplate> <HeaderTemplate> <table style="position: static"> <tr> <td style="width: 100px"> ocoplvl: </td> <td style="width: 100px">  ochbmcus:</td> <td style="width: 100px"> occphase:</td> <td style="width: 100px"> ochbplan: </td> <td style="width: 100px"> ochbelev:</td> <td style="width: 100px"> ocooption:</td> <td style="width: 100px"> ocdlo1:</td> <td style="width: 100px">  occspr:</td> </tr> </table> </HeaderTemplate> </asp:DataList></div> <span style="color: #0000ff"><br /> </span> <br /> </form></body></html>

|||Oh that's great, I'm glad to see you've solved the problem. Really thank you very much for sharing your experience with us!

Cheers,sql

Friday, March 30, 2012

If numeric query

If numeric query

If Not Exists?

If Not Exists?

If Not Exists?

Wednesday, March 28, 2012

IF EXISTS statement in my perl program

IF EXISTS SQL Question - using Query Analyzer

If Exists capture value returned from stored proc

If Else question URGENT

If Else query for a complex data set

Identity column

Blog Archive

About Me