I need to update a table for each occurance of the ID, but my code is just updating the first instance. Can someone suggest the best way to do a match loop to keep updating records that match the particular ID.

 TABLE FILE RAS-OBO_HDR_TBL
PRINT
     MU_CODE
     VX_NO
  NA_PROFORMA_ID
  NA_DATE_SENT
     COMPUTE NA_DATE_PRINTED/HYYMDS = HGETC(10, 'HYYMDS');
     COMPUTE NA_OUT_VAL/I1 = 1;
  NA_PRINTER
-*BY VX_NO NOPRINT
WHERE NA_OUT_VAL EQ 1 ;
ON TABLE HOLD AS U1
END
-RUN
MODIFY FILE RAS-NA_PROFORMA_PRINT_TRIGGER_TBL
FIXFORM FROM U1
MATCH NA_PROFORMA_ID
ON MATCH UPDATE NA_DATE_PRINTED NA_OUT_VAL
DATA ON U1
END
-RUN
-EXIT
 

This message has been edited. Last edited by: Donald,

Just a suggestion. In your table request, sort your transactions by the fields you are matching on.

Also, post the masters for the input and output files.

Here's an example that works:

TABLE FILE EMPDATA
PRINT
PIN
COMPUTE SALARY1/D12.2M = SALARY + 1.01; AS 'SALARY'
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS H001 FORMAT ALPHA
END
-RUN

MODIFY FILE EMPDATA
FIXFORM FROM H001
MATCH PIN
-*ON MATCH   ACTIVATE SALARY
  ON MATCH   UPDATE SALARY
  ON NOMATCH TYPE "NOT FOUND"
DATA ON H001
END
-RUN

TABLE FILE EMPDATA
PRINT PIN SALARY
END

Are the values for column NA_PROFORMA_ID unique in table RAS-NA_PROFORMA_PRINT_TRIGGER_TBL?

quote:

the values for column NA_PROFORMA_ID

The values for column NA_PROFORMA_ID will be the same for a given group of rows that need to be updated.

Depending on the data source, you may have to reposition the pointer in the modify.

For example, if the first record to be match is the last one in the file, the pointer will be past the last record, a reposition will put the pointer back to the top.

This happens for FOCUS files.

I don't think it is an issue with RDBMS's though.

What data source is the table RAS-NA_PROFORMA_PRINT_TRIGGER_TBL ?

Can you post the master and acx if it has one ?

Unless what Waz describes solves this issue, "The values for column NA_PROFORMA_ID will be the same for a given group of rows that need to be updated" won't work, one input row will update one database row - you cannot use one input row to update many database rows (unless repositioning the pointer works).

In the oracle dadabase I only see the first record updated, but there are three with the same proforma_ID

  SEGNAME=RAS_NA_PROFORMA_PRINT_TRIGGER_TBL, 
   TABLENAME=RASDBT.NA_PROFORMA_PRINT_TRIGGER_TBL, CONNECTION=RASTRN, KEYS=3, $ 

 FILENAME=RAS_NA_PROFORMA_PRINT_TRIGGER_TBL, SUFFIX=SQLORA  , $
  SEGMENT=RAS_NA_PROFORMA_PRINT_TRIGGER_TBL, SEGTYPE=S0, $
    FIELDNAME=MU_CODE, ALIAS=MU_CODE, USAGE=A3, ACTUAL=A3, $
    FIELDNAME=NA_PROFORMA_ID, ALIAS=NA_PROFORMA_ID, USAGE=A17, ACTUAL=A17, $
    FIELDNAME=VX_NO, ALIAS=VX_NO, USAGE=A10, ACTUAL=A10, $
    FIELDNAME=NA_DATE_SENT, ALIAS=NA_DATE_SENT, USAGE=HYYMDs, ACTUAL=HYYMDs, $
    FIELDNAME=NA_DATE_PRINTED, ALIAS=NA_DATE_PRINTED, USAGE=HYYMDs, ACTUAL=HYYMDs,
      MISSING=ON, $
    FIELDNAME=NA_OUT_VAL, ALIAS=NA_OUT_VAL, USAGE=I11, ACTUAL=I4, $
    FIELDNAME=NA_PRINTER, ALIAS=NA_PRINTER, USAGE=A50V, ACTUAL=A50V, $ 

Actually you can use one input row to update many, but you will need to use NEXT after the first MATCH.

The process is:

• MATCH to position the cursor on the first record.
  
• If matched, then go to a new CASE.
  
• The new case will have a loop with NEXT.
  

Next is like MATCH, except it gets the next record.

You can then do ON NEXT UPDATE, and ON NONEXT GOTO TOP, this gets the next input record.

Because the datasoruce is Oracle, the MATCH will return a result set of all the records that match the input value.

I believe the NEXT solution will work.

OK , Thanks WAZ. I never used the next routine but I will try it.

Here is the code that I am trying to use, but it is still just updating one record.

 MODIFY FILE RAS-NA_PROFORMA_PRINT_TRIGGER_TBL
FIXFORM FROM U1
MATCH NA_PROFORMA_ID
ON MATCH UPDATE NA_DATE_PRINTED NA_OUT_VAL
ON MATCH GOTO NEXTVX
ON NOMATCH GOTO TOP
 
CASE NEXTVX
NEXT NA_PROFORMA_ID
ON NEXT UPDATE NA_DATE_PRINTED NA_OUT_VAL
ON NEXT GOTO NEXTVX
ON NONEXT GOTO ENDCASE 
ENDCASE
DATA ON U1
END
-RUN
-EXIT 

You need to MATCH on more than that one field because it is not a unique key.
MATCH on enough fields that make a unique key.

Just a thought, this is an oracle table, why not just issue SQL to update ?