|
midrange.com
Well, good... I was finally able to contribute something useful instead of
creating noise. :-)
Michael Schutte
"Lim Hock-Chai"
<Lim.Hock-Chai@us
amobility.com> To
Sent by: <rpg400-l@xxxxxxxxxxxx>
rpg400-l-bounces@ cc
midrange.com
Subject
RE: Constructing Sql to position
06/02/2006 05:51 phone number that breakup
PM inareacode, prefix, suffix and
extension
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Thanks Michael.
Your sql statement appears to be working great. Sql engine uses an
existing logical (Not the one that I expected) and there is not speed
problem.
-----Original Message-----
From: Michael_Schutte@xxxxxxxxxxxx [mailto:Michael_Schutte@xxxxxxxxxxxx]
Sent: Friday, June 02, 2006 10:21 AM
To: Lim Hock-Chai
Cc: Hauser, Birgitta
Subject: Fw: Constructing Sql to position phone number that breakup in
areacode, prefix, suffix and extension
I'm sending you this privately. If it works, you may post it to the group.
I was just trying to cut down on the noise.
InputNbr = 614-555-5555
Where (AreaCode = Substr(:InputNbr, 1, 3) And
Prefix = Substr(:InputNbr, 4, 3) And
Suffix = Substr(:InputNbr, 7, 4) And
Extension >= Substr(:InputNbr, 10, 3))
OR
(AreaCode = Substr(:InputNbr, 1, 3) And
Prefix = Substr(:InputNbr, 4, 3) And
Suffix > Substr(:InputNbr, 7, 4))
OR
(AreaCode = Substr(:InputNbr, 1, 3) And
Prefix > Substr(:InputNbr, 4, 3))
OR
(AreaCode > Substr(:InputNbr, 1, 3))
If Record has 614-555-5555 ext 1111, first where statement will select it.
If Record has 614-555-5556 ext xxxx, second statement will select it.
If Record has 614-556-xxxx ext xxxx, third statement will select it.
If record has 714-xxx-xxxx ext xxxx, last statement will select it.
I used xxx and xxxx to mean "anything enter"
Michael Schutte
Michael
Schutte/Bob Evans
To
06/02/2006 10:55 "Lim Hock-Chai"
AM <Lim.Hock-Chai@xxxxxxxxxxxxxxx>
cc
Subject
Fw: Constructing Sql to position
phone number that breakup in
areacode, prefix, suffix and
extension
Sorry this won't work either.
Michael Schutte
----- Forwarded by Michael Schutte/Bob Evans on 06/02/2006 10:55 AM -----
Michael
Schutte/Bob Evans
To
06/02/2006 10:50 RPG programming on the AS400 /
AM iSeries <rpg400-l@xxxxxxxxxxxx>
cc
Subject
RE: Constructing Sql to position
phone number that breakup in
areacode, prefix, suffix and
extension(Document link: Michael
Schutte)
Just jotting something down... look it over and see if it will work...
(not sure about performance either)
Select AreaCode, Prefix, Suffix, Extenstion
From TelefoneFile
*** This will get you 614-555-5555 ext 5555 or whatever...
Where (AreaCode = Substr(:InputNbr, 1, 3) And
Prefix >= Substr(:InputNbr, 4, 3) And
Suffix >= Substr(:InputNbr, 7, 4) And
Extention >= Substr(:InputNbr, 10, 3))
OR
*** Then this should get every other number greater than the area code.
(AreaCode > Substr(:InputNbr, 1, 3) And
Order by AreaCode, Prefix, Suffix, Extention
I suspect that if 614-555-5555 was entered, that it should be the first
record.
Michael Schutte
"Hauser,
Birgitta"
<Birgitta.Hauser@ To
LP-GMBH.com> "'rpg400-l@xxxxxxxxxxxx'"
Sent by: <rpg400-l@xxxxxxxxxxxx>
rpg400-l-bounces@ cc
midrange.com
Subject
RE: Constructing Sql to position
06/02/2006 10:28 phone number that breakup in
AM areacode, prefix, suffix and
extension
Please respond to
RPG programming
on the AS400 /
iSeries
<rpg400-l@midrang
e.com>
Hi Lim,
Your sql statement will only result in the phone number entered. The subfile display requires listing phone numbers starting from the InputNbr.
Then try the following: (But be aware, I'm not sure about the performance)
Select AreaCode, Prefix, Suffix, Extenstion
From TelefoneFile
Where AreaCode >= Substr(:InputNbr, 1, 3)
or AreaCode = Substr(:InputNbr, 1, 3)
and Prefix >= Substr(:InputNbr, 4, 3)
or AreaCode = Substr(:InputNbr, 1, 3)
and Prefix = Substr(:InputNbr, 4, 3)
and Suffix >= Substr(:InputNbr, 7, 4)
or AreaCode = Substr(:InputNbr, 1, 3)
and Prefix = Substr(:InputNbr, 4, 3)
and Suffix = Substr(:InputNbr, 7, 4)
And Extention >= Substr(:InputNbr, 10, 3)
Mit freundlichen Grüßen
i.A. Birgitta Hauser
LUNZER + PARTNER GMBH
Consulting | Software | Service
Carl-Zeiss-Straße 1
63755 Alzenau
Tel: + 49 6023 951-255
Fax: + 49 6023 951-111
Internet. www.lp-gmbh.com <http://www.lp-gmbh.com/>
www.rpg-schulung.de <http://www.rpg-schulung.de>
--------------------------------------------------------
Handelsregister: Aschaffenburg HRB 4720
Gerichtsstand und Erfüllungsort Alzenau
Ust-IdNr.: DE132093146
Geschäftsführer: Rudolf Gerbert
---------------------------------------------------------
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
