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?
WebFOCUS 8204, Unix, Windows
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.
|Powered by Social Strata|