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,

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?

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.