Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Trouble Using WHERE IN FILE
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Trouble Using WHERE IN FILE
 Login/Join
 
Gold member
posted
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,


WebFOCUS 8204
 
Posts: 59 | Registered: July 10, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
IN FILE expects a single column of data.

You will need to remove one of the ESSN fields.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6018 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 8204, Unix, Windows
 
Posts: 1527 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Also, if ESSN is a string it must not be a variable length (A10V), it has to be fix (A10).


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 1990 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Gold member
posted Hide Post
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 Smiler .


WebFOCUS 8204
 
Posts: 59 | Registered: July 10, 2018Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Trouble Using WHERE IN FILE

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.