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
Charles

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(tblRegiste
r.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.googlegro
ups.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(tblRegiste
r.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>&nbsp;</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>&nbsp;</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://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>&nbsp;</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(tblRegiste
r.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&nbsp;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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
> <DIV><FONT face=Arial size=2>&lt;</FONT><A
> href=" mailto:cbanks@bjtsup
port.com"><FONT face=Arial
> size=2>cbanks@bjtsupport.com</FONT></A><FONT face=Arial size=2>&gt; wrote 
in
> message </FONT><A
> href="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>&gt;I have a tab
le
> with a memo field that sometimes contains an account<BR>&gt; number at the
 same
> location.&nbsp; I need to find a record related to that<BR>&gt; accoun
t in
> another table.&nbsp; I can write and run a query for a record<BR>&gt; 
which
> works fine if the account number exists, but (obviously) get an<BR>&gt; er
ror if
> it does not.&nbsp; I wrote the following query which I don't<BR>&gt; u
nderstand
> why it will not work.<BR>&gt; <BR>&gt; IF EXISTS (SELECT fldMemo FROM
> tblRegister WHERE ISNUMERIC(SUBSTRING<BR>&gt; (tblRegister.fldMemo,6,9)) =
> 1)<BR>&gt; <BR>&gt; BEGIN<BR>&gt; SELECT fldChkNo, fldDate, fldName, fldMe
mo,
> fldAmt, fldTransmitted,<BR>&gt; fldStatus, fldLogDate, fldNote<BR>&gt; FRO
M
> tblRegister INNER JOIN tblNotes ON SUBSTRING<BR>&gt; (tblRegister.fldMemo,
6,9) =
> tblNotes.fldMemberNo<BR>&gt; END<BR>&gt; <BR>&gt; ELSE<BR>&gt; <BR>&gt;
> BEGIN<BR>&gt; SELECT fldChkNo, fldDate, fldName, fldMemo, fldAmt,
> fldTransmitted,<BR>&gt; fldStatus, fldLogDate<BR>&gt; FROM tblRegister<BR>
&gt;
> END<BR>&gt; <BR>&gt; Any help on this would be greatly appreciated.<BR>>
;
> Thanks<BR>&gt; Charles<BR>&gt;</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:uFkIfW
P$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