Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Trouble Using WHERE IN FILE
 Login/Join
 
Brandon Andrathy
Platinum 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: 152 | Registered: July 10, 2018Report This Post
Waz
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.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
BabakNYC
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 8206, Unix, Windows
 
Posts: 1853 | Location: New York City | Registered: December 30, 2015Report This Post
MartinY
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.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Brandon Andrathy
Platinum 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: 152 | Registered: July 10, 2018Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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

Copyright © 1996-2020 Information Builders