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     [CLOSED] JOIN Not Working As Intended

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] JOIN Not Working As Intended
 Login/Join
 
Platinum Member
posted
Hey All,

I have a work around for the issue I'm experiencing but it's really bugging me why the JOIN I'm doing isn't filtering down my records. Could someone look at my code below and explain what I'm doing wrong/why it's not working?

 

TABLE FILE _ADM_STAR
SUM
	 COMPUTE OFFICE_VISITS;
	 COMPUTE PCP_FLAG;
BY  WEBSITE
BY  ESSN
WHERE WEBSITE EQ &&WEBSITE;
WHERE GROUPCD EQ &&GROUPCD;
WHERE EMPLOYERCD EQ &EMPLOYERCD;
WHERE PLANCD EQ &PLANCD;
WHERE LEVELID EQ &LEVEL1ID;
WHERE LEVELID EQ &LEVEL2ID;
WHERE PLANTYPECD EQ &PLANTYPECD.QUOTEDSTRING;
WHERE SPECIALTYTYPE EQ 'PCP';
WHERE (( &DATEFIELD GE '&STARTDATE3_LB' ) AND ( &DATEFIELD LT '&STARTDATE3' ));
WHERE ESSN NE '-1'
WHERE TOTAL PCP_FLAG EQ 1
ON TABLE HOLD AS 'LOOKBACK' FORMAT FOCUS
END

TABLE FILE _ADM_STAR
SUM
	COMPUTE Admits;
	COMPUTE SUM_MEMBER_MONTH;
BY  WEBSITE
BY  PERIOD
BY  FULLDATE_YEAR_Y
BY  ESSN
WHERE (( &DATEFIELD GE '&STARTDATE3' ) AND ( &DATEFIELD LE '&ENDDATE3' ))
WHERE WEBSITE EQ &&WEBSITE;
WHERE GROUPCD EQ &&GROUPCD;
WHERE EMPLOYERCD EQ &EMPLOYERCD;
WHERE PLANCD EQ &PLANCD;
WHERE LEVELID EQ &LEVEL1ID;
WHERE LEVELID EQ &LEVEL2ID;
WHERE PLANTYPECD EQ &PLANTYPECD.QUOTEDSTRING;
WHERE _ADM_STAR.COC.COCPARENTDESC EQ 'Inpatient Hospital';
ON TABLE HOLD AS 'CURRENT' FORMAT FOCUS
END

JOIN FILE CURRENT AT WEBSITE TAG A1 TO UNIQUE
FILE LOOKBACK AT WEBSITE TAG B1
WHERE A1.WEBSITE EQ B1.WEBSITE;
WHERE A1.ESSN EQ B1.ESSN;
END

TABLE FILE CURRENT
SUM
	SUM_MEMBER_MONTH
	Admits
	PCP_FLAG
BY  WEBSITE
BY  PERIOD
BY  FULLDATE_YEAR_Y
BY ESSN
END

 


The last TABLE FILE "CURRENT" after the JOIN code should filter down the ESSN fields down but it doesn't. It still has the same amount of values as in the first "CURRENT" table.

Really bugging me why this isn't working! I have gotten it to work with other data sets. I'm wondering if it has something to do with the type of data it is? The ESSN field in the system is Alphanumeric. I feel like Joins should work for that though.

Thanks in advance!

This message has been edited. Last edited by: Brandon Andrathy,


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report This Post
Virtuoso
posted Hide Post
I'm pretty sure your JOIN TO table has to have an Index. Can you add INDEX syntax to your ON TABLE HOLD and see if that makes a difference?


WebFOCUS 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
Platinum Member
posted Hide Post
Hey Babak,

Thanks for your response. So I added INDEX ESSN to the LOOKBACK Table and then I changed my JOIN syntax so I was joining the CURRENT table up to the indexed table.

 JOIN FILE LOOKBACK AT WEBSITE TAG A1 TO UNIQUE
FILE CURRENT AT WEBSITE TAG B1
WHERE A1.WEBSITE EQ B1.WEBSITE;
WHERE A1.ESSN EQ B1.ESSN;
END 


When querying against the Lookback table, I got the data I was looking for.

Shouldn't I be able to filter down the ESSNs in the broader table by the ESSNs in the indexed table though? When I added the index code and left my join as is in the code in my first post. Nothing changed. Maybe that's something I wasn't clear on in WEBFOCUS that you need to query based off the Indexed table.


WebFOCUS 8204
 
Posts: 152 | Registered: July 10, 2018Report 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     [CLOSED] JOIN Not Working As Intended

Copyright © 1996-2020 Information Builders