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] Where conditions using file is not working
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Where conditions using file is not working
 Login/Join
 
Platinum Member
posted
Hi,
I am using where conditions based on values from an external file.But it is not filtering out the rows and printing all the rows.
Also, The next where condition in which i am printing the rows with null values for the filter column is not working.

If i just use the second filter condition alone i am getting 0 rows

Where condition

(( NOT CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE IN FILE SEL1) OR (CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE NE MISSING))

This message has been edited. Last edited by: FP Mod Chuck,
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Do you get an error?
Do you have a FILEDEF for SEL1?
What's the format of DURATION_TYPE_CODE?


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I didnt get any error.
My filter is not weeding out three rows.
I have a filedef for SEL1 file
Data type of the column : A6.
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
As a test, I'd remove the OR'd part and the NOT and see if DURATION_TYPE_CODE IN FILE SEL1 works. If that works then focus on the NOT and then on the OR.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Something weird happened.
It shows file sel 1 is empty.
When i open the ftm file is see values.

VALUES IN FILE
EMSLRD
EMCMSO
EMOTHR
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Your FILEDEF please.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
FILEDEF SEL1 DISK ZZ2016METADATA/SEL1
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Please add (LRECL 6 RECFM F to make it more clear.
  
FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 (LRECL 6 RECFM F


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
quote:
FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 (LRECL 6 RECFM F

I am getting the same error
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
What if you change the physical file name to lowercase and include .ftm?

  
FILEDEF SEL1 DISK zz2016metadata/sel1.ftm (LRECL 6 RECFM F 


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
It is working now.
But IN condition has taken only the first value from the file
EMSLRD
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
LRECL 6 and RECFM F assume the .ftm you're reading is an ALPHA file with the values stacked on top of each other the way you show them. If you're creating this list using an ON TABLE HOLD AS SEL1 then make sure to add FORMAT ALPHA to create a stacked list of values in a plane text file.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
This is how i am creating my file.

TABLE FILE ZZ2016METADATA/CLS_ELIGIBILITY_S
PRINT COMPUTE Q/A6 = CLS_ELIGIBILITY_S.PLAN_ELGBLTDT_EXCLD_EMPL_RLSHP.EMPLOYEE_DURATION_TYPE_CODE; AS CODES
WHERE ( CLS_ELIGIBILITY_S.ORGANIZATION_ROLE_1.Plan_No EQ '************' );
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS ZZ2016METADATA/SEL1 FORMAT ALPHA
END
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Add a line to the bottom and type

? FILEDEF

That'll tell you exactly what the LRECL and RECFM is. Use those values.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Thank you Babak,The NOT IN worked.
But now the OR condition is not working.

Is there a conflict between NOT IN and NULLS
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
When you OR two conditions you're saying I want either condition to be true. If you want to make sure to omit all NULLs and the values not in SEL1 then, you might want to try AND instead of OR.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I want to have NULLS and the omitting the values from SEL1
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
I doubt adding that OR will force null values to show. You're saying

CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE NE MISSING

Which means only show values that are not null.

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


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Sorry That was a typo.

((NOT CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE IN FILE SEL1) OR (CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE EQ MISSING));

It is not working even when i use (CLS_ELIGIBILITY_S.ACCOUNT_DURATION_DETAIL.DURATION_TYPE_CODE EQ MISSING))
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Can you test only for missing? Let's see how many missing DURATIOIN_TYPE_CODEs there are first.

EQ MISSING is the same as IS MISSING. Do a COUNT to make sure you find out how many to expect.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
Platinum Member
posted Hide Post
when i use missing i get 0 rows.
But i have nulls in that column.
It is printed as '.' in EXCEL
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Platinum Member
posted Hide Post
Hi Babak,
I found the issue.
It is with the code.
We were checking for columns before the join occurs.At that instance there are no nulls.Now i hold the file and then apply filters it is working now.

Thank you so much
 
Posts: 107 | Registered: October 25, 2017Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Outstanding. I'm delighted it's working now.


WebFOCUS 8203, Unix, Windows
 
Posts: 1304 | Location: New York City | Registered: December 30, 2015Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Where conditions using file is not working

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