SQL RRN or ROW update ?

[JT400coder]

Is there a way using sql to update a field in a file on the last record of the file? I want to update data in the last record and the next-to-last record; however, I don't seem to be able to use the RRN or ROWCOUNT keyworks properly to do this...even if it's possible. Does anyone have a sample I could follow? Thanks. - JT

[DeadManWalks]

Re: SQL RRN or ROW update ?

My file has 40 records in it.

select rrn(X12I820D22), id22refdes                               
  from X12I820D22                                                
where rrn(x12i820d22) + 1 >= ( Select Max(rrn(X12I820D22)) from  
 X12I820D22 ) 

B4

....+....1....+....2....+....3.
 RRN ( X12I820D22 )   Ref      
                      Desc     
                 39   LAST 2   
                 40   LAST 2   
********  End of data  ******** 

update X12I820D22 Set  id22refdes   = 'UPDATED'                 
                                                                
where rrn(x12i820d22) + 1 >= ( Select Max(rrn(X12I820D22)) from 
 X12I820D22 ) 

After;

....+....1....+....2....+....3.
 RRN ( X12I820D22 )   Ref      
                      Desc     
                 24            
                 25            
                 26            
                 27            
                 28            
                 29            
                 30            
                 31            
                 32            
                 33            
                 34            
                 35            
                 36            
                 37            
                 38            
                 39   UPDATED  
                 40   UPDATED  
********  End of data  ******** 

This works if the RRNs are in sequence. That is if 39 was deleted prior to the update, 38 would not be updated. Hey , its a start.

[JT400coder]

Re: SQL RRN or ROW update ?

Thanks....this worked for me as well:

update qtemp/achrtnptx set rectype = '9' where RRN(achrtnptx) =
(select count(*) from qtemp/achrtnptx)

- JT

[DeadManWalks]

Re: SQL RRN or ROW update ?

Cool, I sometimes get it right. Now I just have to find out what it is.

[kausix777]

Re: SQL RRN or ROW update ?

Isn't count(*) risky?
What if there are deleted records in the file?

[DeadManWalks]

Re: SQL RRN or ROW update ?

Hey, Count(*) does not care about deleted records.

[kausix777]

Re: SQL RRN or ROW update ?

Right, but does the RRN?

[DeadManWalks]

Re: SQL RRN or ROW update ?

RRN() gives you the relative record number of the record. Deleted records are not used.

[kausix777]

Re: SQL RRN or ROW update ?

Hmm.. I seem to have been confused by something.
Then when would the RRNs not be in sequence as in "This works if the RRNs are in sequence". I guess you meant the RRN having the same sequence as the order by which the file is keyed.

[DeadManWalks]

Re: SQL RRN or ROW update ?

I refer back to my original statement.

This works if the RRNs are in sequence. That is if 39 was deleted prior to the update, 38 would not be updated. Hey , its a start.

If you have a file that has records deleted from it, the rrn() will give you the correct RRN of the record, but my simple logic will not get the next to last record as in the below example;

 Control . . .                        
            1         2               
   Record#  Control # Tpid            
                                      
                                      
         1  000000076 13              
         2  000000077 13              
         3  000000078 13              
         4  000000079 11              
         5  000000080 11              
         6  000000081 11              
         8  000000083 11 

There is no RRN = 7. So only the Max of 8 will be updated.
My intent was to just to post a quick and dirty example.

[kpmac]

Re: SQL RRN or ROW update ?

You should be able to incororate something like this into an update stament.
The SQL below will return the first two RRNs in descending order and asociate them to a row with an IN statement. You should also be able to accomplish the same with a cartesian join but I did not have time to test thoroughly.

Note: This wont work if you are reuseing deleted records.

Select
      *

From
      MyTable.MyFile
Where
      RRN(MyTable.MyFile) IN
      ( Select *
        From (
               Select
                    RRN(MyTable.MyFile) as recno
               From
                    MyTable.MyFile
               Order by 1 Desc
               Fetch First 2 Rows Only
              ) as x
        )