RE: Modiiyng a field value -- RPG400-L

c/exec SQL
C+  update ORDINV a
C+   set promo = (select coalesce(max(b.promo),0)
C+                from ordinv b
C+                where a.cust = b.cust
C+                  and a.invno = b.invno
C+                  and b.promo <> 0
C+   where a.promo = 0
c/end-exec

If you're absolutely sure that you'll never have

  5 LKJ   18  000 
  6 LKJ   18  321 
  7 LKJ   18  123

You can drop the MAX().

If you'd want 123 to be chosen instead of 321, then use MIN().

HTH,

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
  

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx 
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Yuriy Veletnik
> Sent: Monday, May 01, 2006 11:20 AM
> To: rpg400-l@xxxxxxxxxxxx
> Subject: Modiiyng a field value
> 
> I have a file with 4 fields:
> 
> Columns . . . :    1  80                                      Browse
> 
> 
>  SEU==>
> 
> 
>  FMT A* .....A*. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6
> ...+.
> 
>         *************** Beginning of data
> ********************************
> 
> 0001.00      A* TEMPORARY FILE FOR EARLY BUY FIX
> 
> 
> 0002.00      A          R ORDINV
> 
> 
> 0003.00      A            ORDNO          3S 0       
> TEXT('ORDER NUMBER')
> 
> 
> 0004.00      A            CUST           5          TEXT('CUSTOMER
> NAME') 
> 
> 0005.00      A            INVNO          3S 0       TEXT('INVOICE #')
> 
> 
> 0006.00      A            PROMO          3S 0       TEXT('PROMOTION
> CODE')
> 
> 0007.00      A          K ORDNO
> 
> 
>  
> 
> The data I'm trying to change is:
> 
>  
> 
> Original file data: 
>  
>   1 XYZ   11  654 
>   2 ABC   15  654 
>   3 ABC   15  000 
>   4 LKJ   18  000 
>   5 LKJ   18  000 
>   6 LKJ   18  321 
>   7 LKJ   18  000 
>   8 LKJ   18  000 
>   9 FGH   23  000 
>  10 FGH   23  000 
>  11 FGH   23  000 
>  12 FGH   23  987 
>  13 JKL   48  000 
>  14 JKL   48  000 
>  15 JKL   48  000 
>  
> I'm trying to change it to: 
>  
>   1 XYZ   11  654 
>   2 ABC   15  654 
>   3 ABC   15  654 
>   4 LKJ   18  321 
>   5 LKJ   18  321 
>   6 LKJ   18  321 
>   7 LKJ   18  321 
>   8 LKJ   18  321 
>   9 FGH   23  987 
>  10 FGH   23  987 
>  11 FGH   23  987 
>  12 FGH   23  987 
>  13 JKL   48  000 
>  14 JKL   48  000 
>  15 JKL   48  000
> 
>  
> 
> So, whenever there is a value in the PROMO field it has to be 
> populated
> into the records with the same value in the INVNO field.
> 
>  
> 
> To do that I created a logical file keyed on the PROMO + INVNO fields.
> I'm trying to read a file from the end to the begging and replace a
> PROMO value.
> 
> Logical file and a program as following:
> 
>  
> 
>  
> 
> SEU==>                                                
> 
>  FMT FX FFilename++IPEASF.....L.....A.Device+.Keywords+
> 
>         *************** Beginning of data *************
> 
> 0001.00 FORDINVL   UF   E           K DISK             
> 
> 0002.00                                                
> 
> 0003.00 D PROMO_S         S              3  0          
> 
> 0004.00 D INVNO_S         S              3  0          
> 
> 0005.00                                                
> 
> 0006.00 C     *HIVAL        SETGT     ORDINVL          
> 
> 0007.00 C                   READP     ORDINVL          
> 
> 0008.00                                                
> 
> 0009.00 C                   EVAL      INVNO_S = INVNO  
> 
> 0010.00 C                   EVAL      PROMO_S = PROMO  
> 
> 0011.00                                                
> 
> 0012.00 C                   DOW       NOT %EOF         
> 
> 0013.00 C                   READP     ORDINVL          
> 
> 0014.00 C                   IF        INVNO = INVNO_S  
> 
> 0015.00 C                   IF        PROMO_S <> 0     
> 
> 0016.00 C                   EVAL      PROMO = PROMO_S  
> 
> 0017.00 C                   UPDATE    ORDINV           
> 
> 0018.00 C                   ENDIF                      
> 
> 0019.00 C                   ELSE                       
> 
> 0020.00 C                   EVAL      INVNO_S = INVNO  
> 
> 0021.00 C                   EVAL      PROMO_S = PROMO  
> 
> 0022.00                                                
> 
> 0023.00 C                   ENDIF                      
> 
> 0024.00                                                
> 
> 0025.00 C                   ENDDO                      
> 
> 0026.00                                                
> 
> 0027.00 C                   EVAL      *INLR = *ON      
> 
> 0028.00 C                   RETURN                     
> 
> 0029.00                                                
> 
>                                                        
> 
>  
> 
>  
> 
>  
> 
>  
> 
> FMT LF .....A..........T.Name++++++.Len++TDpB......Functions++++++
> 
>         *************** Beginning of data *************************
> 
> 0001.00      A          R ORDINV                    PFILE(ORDINV)  
> 
> 0002.00      A          K INVNO                                    
> 
> 0003.00      A          K PROMO                                    
> 
>         ****************** End of data ****************************
> 
>  
> 
> I get and error message:
> 
>  
> 
> Update or delete in file ORDINVL without prior input operation (C G D
> F).
> 
>  
> 
>  
> 
> Please help me fix that error or is there another solution for the
> problem?
> 
>  
> 
>  
> 
> Yuriy Veletnik
> 
> AS/400 Administrator
> 
> Trade Associates Group, ltd
> 
> 1730 W. Wrightwood
> 
> Chicago, IL 60614
> 
> 773.871.1300 x130
> 
>  
> 
> -- 
> 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.
> 
>