I am trying to limit down some records in my first table file called "PCP_ESSN_LIST" and then use those records in a WHERE statement to filter the table based on the "ESSN" field. When I use "WHERE ESSN IN FILE 'PCP_ESSN_LIST', I get 0 records back. The format of the ESSNs are A9V. If
TABLE FILE PCP_UTIL_LIST_1-YEAR_LOOKBACK SUM ESSN BY ESSN WHERE _ADM_STAR.WEBSITE.WEBSITE EQ 'chicaremanagement'; WHERE _ADM_STAR.CLAIM_EMPLOYER.GROUPCD EQ '15044'; WHERE _ADM_STAR.DIMPROVIDERPOC.SPECIALTYTYPE EQ 'PCP'; WHERE ESSN NE '-1' WHERE TOTAL CNT_E_M_OFFICE_VISITS GE 1 ON TABLE HOLD AS 'PCP_ESSN_LIST' END TABLE FILE _ADM_STAR SUM _ADM_STAR.MEMBER.ESSN BY _ADM_STAR.MEMBER.ESSN WHERE ESSN IN FILE PCP_ESSN_LIST WHERE _ADM_STAR.WEBSITE.WEBSITE EQ 'chicaremanagement'; WHERE _ADM_STAR.CLAIM_EMPLOYER.GROUPCD EQ '15044'; END
I am expecting to receive about 10,000 records as I would in the first hold file but I get 0 back. If I remove the WEBSITE and GROUPCD filters in my second file, I get a few records but they are all weird looking numbers with 5 leading zeros. I feel like I'm doing something wrong here. Does anyone know what I need to do to fix my code?
Thanks in advance!!This message has been edited. Last edited by: Brandon Andrathy,
IN FILE expects a single column of data.
You will need to remove one of the ESSN fields.
1. What's the format of the column ESSN?
2. Change the first request by removing SUM ESSN
3. Add this code right before the hold syntax: ON TABLE SET HOLDLIST PRINTONLY and get rid of the quotes around the hold file name (HOLD AS 'PCP_ESSN_LIST').
4. Add a -RUN between the two TABLE requests.
5. Test this example and see if it works.
TABLE FILE CAR BY COUNTRY WHERE COUNTRY EQ 'ENGLAND' OR 'JAPAN' ON TABLE SET HOLDLIST PRINTONLY ON TABLE HOLD AS LIST_OF_VALUES END -RUN TABLE FILE CAR SUM RETAIL DEALER SALES BY COUNTRY BY CAR BY MODEL WHERE COUNTRY IN FILE LIST_OF_VALUES ENDThis message has been edited. Last edited by: BabakNYC,
WebFOCUS 8204, Unix, Windows
Also, if ESSN is a string it must not be a variable length (A10V), it has to be fix (A10).
WF versions : Prod 220.127.116.11M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
Thank you all!
It was the format of the field. When I did a define to change the format from A9V to A9, it works as intended .
|Powered by Social Strata|