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

---------- Forwarded Message -----------
From: "Al Macintyre" at work
To: Al at home, which is an M-L subscriber 
Sent: Tue, 18 Mar 2008 13:49:21 -0500
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

------- End of Forwarded Message -------


This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].