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
January 08, 2019, 11:28 AM
BabakNYC
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
January 08, 2019, 11:59 AM
Brandon Andrathy
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.