The proble (I think) is that POENDT is defined as numeric (irrespective of
edit code)and you are attempting to substr a numeric
try the following
Select * from OHIOF.POHEAD P LEFT OUTER JOIN OHIOF.APVN01 V ON P.POVNBR
= V.VNNBR WHERE VNNAME LIKE 'E.M.J. ' AND SUBSTRING(digits(POENDT),5,2)=10
FETCH
FIRST 40 ROWS ONLY
Im fairly sure this will reinstate (if thats the correct word) the leading
zero
Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill
dale janus
<dalejanus@specia
ltypipe.com> To
Sent by: midrange-l@xxxxxxxxxxxx
midrange-l-bounce cc
s@xxxxxxxxxxxx
Subject
SQL query fails, confused over
03/29/2010 12:14 leading character of 6 digit field
PM
Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>
We are writing a PHP program to select our purchase orders based on a
field in the header record. It's an old file, so the date is a six
digit signed decimal field format MMDDYY. The A spec that defined it
also uses a Y edit code.
A POENDT 6S 0 COLHDG('DATE' 'ENTERED')
A
EDTCDE(Y)
We want to select by year, so all we need are the last 2 characters of
the field. This works ok for months 10, 11 and 12 when the field is
full. For the other 9 months, when there is a zero in the first
position, it gives fetch failure errors.
Select * from OHIOF.POHEAD P LEFT OUTER JOIN OHIOF.APVN01 V ON P.POVNBR
= V.VNNBR WHERE VNNAME LIKE 'E.M.J. ' AND SUBSTRING(POENDT,5,2)=10 FETCH
FIRST 40 ROWS ONLY
Viewing the file on the i, it clearly has a leading zero. But SQL is
somehow missing this.
At some time in the future we will add a time/datestamp field, but for
now, this is what we have to work with.
Any suggestions?
---Dale
IMPORTANT: This email is intended for the use of the individual
addressee(s) named above and may contain information that is
confidential, privileged or unsuitable for overly sensitive persons with
low self-esteem, no sense of humor or irrational religious beliefs. If
you are not the intended recipient, any dissemination, distribution or
copying of this email is not authorized (either explicitly or
implicitly) and constitutes an irritating social faux pas. Unless the
word absquatulation has been used in its correct context somewhere other
than in this warning, it does not have any legal or no grammatical use
and may be ignored. No animals were harmed in the transmission of this
email, although the terrier next door is living on borrowed time, let me
tell you. Those of you with an overwhelming fear of the unknown will be
gratified to learn that there is no hidden message revealed by reading
this warning backwards, so just ignore that Alert Notice from Microsoft.
However, by pouring a complete circle of salt around yourself and your
computer you can ensure that no harm befalls you and your pets. If you
have received this email in error, please add some nutmeg and egg
whites, whisk and place in a warm oven for 40 minutes.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
midrange.com
