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,

Do you get an error?
Do you have a FILEDEF for SEL1?
What's the format of DURATION_TYPE_CODE?

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.

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.

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

Your FILEDEF please.

FILEDEF SEL1 DISK ZZ2016METADATA/SEL1

Please add (LRECL 6 RECFM F to make it more clear.

  
FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 (LRECL 6 RECFM F

quote:

FILEDEF SEL1 DISK ZZ2016METADATA/SEL1 (LRECL 6 RECFM F

I am getting the same error

What if you change the physical file name to lowercase and include .ftm?

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

It is working now.
But IN condition has taken only the first value from the file
EMSLRD

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.

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

Add a line to the bottom and type

? FILEDEF

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

Thank you Babak,The NOT IN worked.
But now the OR condition is not working.

Is there a conflict between NOT IN and NULLS

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.

I want to have NULLS and the omitting the values from SEL1

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,

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))

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.

when i use missing i get 0 rows.
But i have nulls in that column.
It is printed as '.' in EXCEL

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

Outstanding. I'm delighted it's working now.