MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » March 2008

RE: SQL String Question



fixed

Thanks, I suspected that you could do substring with SQL but I did not know
the "spelling format" for SUBSTR

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)
<>'FORECAST') ....

HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com

-----Original Message-----
Subject: Fw: SQL String Question

I am working on a program & realized I don't know how to do strings
in SQL.

---------- 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
of sophistication.

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
HCPO<>'FORECAST')

 

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
code.

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/EXEC SQL                                                    
  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') 
  C/END-EXEC                                                    

-

Al Macintyre

BPCS/400 Guru
--
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.


--
WOW! Homepage (http://www.wowway.com)






Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 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