Hard to decipher what the real question is but I think this is the answer:
.... LORD IN (SELECT HORD FROM ECH WHERE SUBSTR(HCPO,1,8)
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
Subject: Fw: SQL String Question
I am working on a program & realized I don't know how to do strings
---------- Forwarded Message -----------
Subject: SQL String Question
The code below is embedded in an RPG/400 program running in V5R1.
It is accessing BPCS customer orders. I need to increase the level
I have one routine to get grand total of orders, then others to get various
exception totals, such as FORECAST, Dec-31, various combinations,
which I subtract from total to get "real" customer orders that will
be shipped the customer.
We have in HCPO (customer purchase order) field the word FORECAST
for the purpose of telling MRP to order raw materials.
Some HCPO contain the word FORECAST then some other stuff ... I urgently
need to learn how to select on the basis of the first 8 columns of
the field HCPO with this text, or re-write the program a different
way. Actually several programs, which are using the same technique.
instead of LORD IN (SELECT HORD FROM ECH WHERE
We use the last day of the year to contain Estimated Annual needs of
the customer that have not yet been filled. So I have the following
LRDTE IN (20081231, 20091231, 20101231, 20111231)
I could advance dates in programs like this each year, if I remember
them all, but I would prefer to select on the basis of the last 4
digits of the 8 digit date.
C+ SELECT SUM(LQORD-LQSHP)
C+ INTO :ORDWRK
C+ FROM ECL WHERE LPROD=:ITEM
C+ AND LCUST=:CUST
C+ AND LICFAC=:FAC
C+ AND LWHS>=:YWHS
C+ AND LWHS<=:ZWHS
C+ AND LID='CL'
C+ AND LCLAS<>'11'
C+ AND LRDTE IN
C+ (20081231, 20091231, 20101231, 20111231)
C+ AND LORD IN
C+ (SELECT HORD FROM ECH WHERE HCPO<>'FORECAST')
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.
This mailing list archive is Copyright 1997-2013 by MIDRANGE dot 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 here. If you have questions about this, please contact