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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Quick Match Syntax Question

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Quick Match Syntax Question
 Login/Join
 
Master
posted
I am somewhat new to all this but have created a *match* which almost yeilds the results I desire. In both tables I have a fields called TRX_PAY_CODE. The trouble is I want BOTH the TRX_PAY_CODE values in my final table; the value found in table1 as well as table2. It gives me only the value from the first table.
In fact, syntatically I do not see how it could differentiate one from the other, not do I know how to tell it to do so. Assisitance is appreciated...THANKS.

MATCH FILE HTEMP
PRINT
HTEMP.HTEMP.GROUP_NUMBER
HTEMP.HTEMP.PATIENT_ID
HTEMP.HTEMP.PAT_MRN
HTEMP.HTEMP.PAT_NM
HTEMP.HTEMP.Date_Of_Service
HTEMP.HTEMP.TRX_PostingDt
HTEMP.HTEMP.TRX_PAY_CODE
HTEMP.HTEMP.TRX_FSC_Number
HTEMP.HTEMP.CurrentFSC
BY HTEMP.HTEMP.Invoice_Number
RUN
FILE HTEMP2
PRINT
HTEMP2.HTEMP2.TRX_PAYMENT_AMOUNT
HTEMP2.HTEMP2.TRX_PAY_CODE
BY HTEMP2.HTEMP2.Invoice_Number
AFTER MATCH HOLD AS HTEMP3 OLD-AND-NEW
END
TABLE FILE HTEMP3
PRINT
HTEMP3.HTEMP3.GROUP_NUMBER
HTEMP3.HTEMP3.PATIENT_ID
HTEMP3.HTEMP3.PAT_MRN
HTEMP3.HTEMP3.PAT_NM NOPRINT
HTEMP3.HTEMP3.Invoice_Number
HTEMP3.HTEMP3.Date_Of_Service
HTEMP3.HTEMP3.TRX_PostingDt
HTEMP3.HTEMP3.TRX_FSC_Number
HTEMP3.HTEMP3.CurrentFSC
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Platinum Member
posted Hide Post
Try to set ASNAMES.

At the top of the FEX file initiate SET ASNAMES = ON and in the actual code

Example:

HTEMP.HTEMP.TRX_PAY_CODE AS PAY_CODE_FROM_TABLE1

HTEMP2.HTEMP2.TRX_PAY_CODE AS PAY_CODE_FROM_TABLE2

NOW IN HTEMP3 you can call those 2 alias names


WebFOCUS - ver8201
[ReportingServers: Windows 64bit;
Client: tomcat and IIS on windows 2012
AppStudio

 
Posts: 104 | Location: Indianapolis | Registered: November 08, 2007Report This Post
Master
posted Hide Post
THANKS! I had tried using the AS verb but it did not run...I have since added the SET command and then amended my table1 amnd table2 routines and now it takes that syntax and allows me to output each individually after the match!!!

HOWEVER..I now have another problem that is somehow a byproduct of all that.

Table1 has 44 records. Table2 had 1.9 million. After the match I should get at most 44 records...in fact I was getting just that until I issues the SET AS NAMES and brought in both fields.

Now I get 1.9M records!!!!

MATCH FILE HTEMP
PRINT HTEMP.HTEMP.GROUP_NUMBER HTEMP.HTEMP.PATIENT_ID HTEMP.HTEMP.PAT_MRN HTEMP.HTEMP.PAT_NM HTEMP.HTEMP.Date_Of_Service HTEMP.HTEMP.TRX_PostingDt HTEMP.HTEMP.TRX_FSC_Number HTEMP.HTEMP.CurrentFSC HTEMP.HTEMP.TXPAYCD1
BY HTEMP.HTEMP.Invoice_Number
RUN
FILE HTEMP2
PRINT HTEMP2.HTEMP2.TRX_PAYMENT_AMOUNT HTEMP2.HTEMP2.TXPAYCD2
BY HTEMP2.HTEMP2.INV
AFTER MATCH HOLD AS HTEMP3 OLD-AND-NEW
END
TABLE FILE HTEMP3
PRINT
HTEMP3.HTEMP3.INV
HTEMP3.HTEMP3.TRX_PAYMENT_AMOUNT
HTEMP3.HTEMP3.TXPAYCD1
HTEMP3.HTEMP3.TXPAYCD2
HTEMP3.HTEMP3.GROUP_NUMBER
HTEMP3.HTEMP3.PATIENT_ID
HTEMP3.HTEMP3.PAT_MRN
HTEMP3.HTEMP3.PAT_NM NOPRINT
HTEMP3.HTEMP3.Invoice_Number
HTEMP3.HTEMP3.Date_Of_Service
HTEMP3.HTEMP3.TRX_PostingDt
HTEMP3.HTEMP3.TRX_FSC_Number
HTEMP3.HTEMP3.CurrentFSC
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END


Only difference I can see is the references to: TXPAYCD1
and TXPAYCD2


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Guru
posted Hide Post
I thin kthat you need to check your code and make sure that you are matching on the same Field. In your lastest post the "BY" field names from HTEMP and HTEMP2 do not match.


WF 7.6.11
Oracle
WebSphere
Windows NT-5.2 x86 32bit
 
Posts: 398 | Registered: February 04, 2008Report This Post
Virtuoso
posted Hide Post
I suspect your data can have multiple payments per invoice.

You should not use PRINT / PRINT in MATCH FILE, if one-to-many matches are possible and you want to capture them all.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Master
posted Hide Post
quote:
I thin kthat you need to check your code and make sure that you are matching on the same Field. In your lastest post the "BY" field names from HTEMP and HTEMP2 do not match.



Ok, I did not realize the BY files had to have the EXACT same name. Is this true. What if the key values have differing names in the two tables...must you see to it that they are the same. Anyway, I made them the same and I think its now working...I must validate. Thanks!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
further....I have another situation where the BY fields in the two tables to be matched have different names: t1.Invoice_Number (upper and lower case) and t2.INV.

The match won't work....different names I went back to the t2 routine and altered the code to store t2.INV as t2.Invoice_Number. Now it should Match!

I redid the match BUT....it appears Webfocus has altered my t2.Invoice_Number to: t2.INVOICE_NUMBER....it appears as mixed case in the hold file but upper case in the match GUI! So, it still does not work...I assume its because the BY fields are of differing names; one mixed case, one all upper case.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Master
posted Hide Post
and finally...the fields are of differing types...t1.Invoice_Number is AV8 and t2.Invoice_Number is I11.

So my thought was to go back to my original table and add the field: t1.Invoice_Number as: t1.INVOICE_NUMBER using the *AS* variable and also to convert it from Av8 to I11 from the get go.

Does this sound reasonable? What is the code to convert from AV8 to I11 on the fly...

This is a learning experience for sure!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Silver Member
posted Hide Post
The BY fields for match statements need to be the same name and the same format.


You could use a DEFINE FILE statement.
SET ASNAMES = ON

DEFINE FILE T1
INVOICE_NUMBER_I11/I11=EDIT(Invoice_Number);
END

MATCH FILE T1
...
BY INVOICE_NUMBER_I11 AS 'INVOICE_NUMBER'
...



Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
 
Posts: 41 | Registered: September 08, 2008Report This Post
Master
posted Hide Post
THANKS! I am getting there...allbeit slowly!


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
Platinum Member
posted Hide Post
Hi,
Just a small correction. The names of the BY fields don't need to be the same.
You could use the AS syntax for the BY field in the MATCH to "match" them together.
E.g.
MATCH FILE CAR
SUM SALES
BY COUNTRY AS 'MYCOUNTRY'
RUN
FILE OTHERCARS
SUM CONTINENT
BY MYCOUNTRY
END

would also work.


WebFOCUS 8.2.06 mostly Windows Server
 
Posts: 195 | Location: Johannesburg, South Africa | Registered: September 13, 2008Report This Post
Master
posted Hide Post
thanks again...I thought I tried that but it did not work...I will try again-it would be easier that way.


WebFOCUS 8206.08
Windows, All Outputs
 
Posts: 603 | Registered: June 28, 2013Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Quick Match Syntax Question

Copyright © 1996-2020 Information Builders