Hello,

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
END

This message has been edited. Last edited by: BabakNYC,

Also, if ESSN is a string it must not be a variable length (A10V), it has to be fix (A10).

Thank you all!

Martin,

It was the format of the field. When I did a define to change the format from A9V to A9, it works as intended .