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

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] JOIN Not Working As Intended
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] JOIN Not Working As Intended
 Login/Join
 
Gold 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: 64 | Registered: July 10, 2018Reply With QuoteReport 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 8204, Unix, Windows
 
Posts: 1617 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Gold 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: 64 | Registered: July 10, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] JOIN Not Working As Intended

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