Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
(SOLVED) match loop
 Login/Join
 
Platinum Member
posted
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,


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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?


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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).


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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, $ 


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
OK , Thanks WAZ. I never used the next routine but I will try it.


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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 


Prod: WebFocus 7.7.3 Win 2003
Dev: WebFocus 7.7.3 Win 2003
 
Posts: 116 | Registered: April 23, 2007Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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.
 
Posts: 142 | Location: Adelaide South Australia | Registered: October 27, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Just a thought, this is an oracle table, why not just issue SQL to update ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6131 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.