 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Where conditions using file is not working
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Where conditions using file is not working Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] Where conditions using file is not working | Login/Join |
| Platinum Member |
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, | ||
|
| Virtuoso |
Do you get an error? Do you have a FILEDEF for SEL1? What's the format of DURATION_TYPE_CODE? WebFOCUS 8206, Unix, Windows | |||
|
| Platinum Member |
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. | |||
|
| Virtuoso |
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 8206, Unix, Windows | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
Your FILEDEF please. WebFOCUS 8206, Unix, Windows | |||
|
| Platinum Member |
FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 | |||
|
| Virtuoso |
Please add (LRECL 6 RECFM F to make it more clear. FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 (LRECL 6 RECFM F WebFOCUS 8206, Unix, Windows | |||
|
| Platinum Member |
I am getting the same error | |||
|
| Virtuoso |
What if you change the physical file name to lowercase and include .ftm? FILEDEF SEL1 DISK zz2016metadata/sel1.ftm (LRECL 6 RECFM F WebFOCUS 8206, Unix, Windows | |||
|
| Platinum Member |
It is working now. But IN condition has taken only the first value from the file EMSLRD | |||
|
| Virtuoso |
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 8206, Unix, Windows | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
Add a line to the bottom and type ? FILEDEF That'll tell you exactly what the LRECL and RECFM is. Use those values. WebFOCUS 8206, Unix, Windows | |||
|
| Platinum Member |
Thank you Babak,The NOT IN worked. But now the OR condition is not working. Is there a conflict between NOT IN and NULLS | |||
|
| Virtuoso |
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 8206, Unix, Windows | |||
|
| Platinum Member |
I want to have NULLS and the omitting the values from SEL1 | |||
|
| Virtuoso |
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 8206, Unix, Windows | |||
|
| Platinum Member |
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)) | |||
|
| Virtuoso |
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 8206, Unix, Windows | |||
|
| Platinum Member |
when i use missing i get 0 rows. But i have nulls in that column. It is printed as '.' in EXCEL | |||
|
| Platinum Member |
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 | |||
|
| Virtuoso |
Outstanding. I'm delighted it's working now. WebFOCUS 8206, Unix, Windows | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Where conditions using file is not working
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [SOLVED] Where conditions using file is not workingCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |