Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.



Read-Only Read-Only Topic
Go
Search
Notify
Tools
(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, 2007Report 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, 2006Report 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, 2005Report 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, 2007Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2005Report 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, 2007Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report 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, 2007Report 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, 2007Report 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: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report 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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic


Copyright © 1996-2020 Information Builders