Variable Filtering Not Working

[jcollier83]

I have a report in which I created a WHERE clause for a flag column (contains a 1 if true, 0 if false). The flag value is generated in the master file using the following logic:

DEFINE DISABLED/A20=IF NSACCOUNTLOCK EQ 'true' THEN '1' ELSE '0'; $

The WHERE clause in my FEX is as follows:

( AD_HOC.ROOT.DISABLED EQ &DISABLED )

When I run my report, I'm prompted to enter a value for &DISABLED. No matter what value I enter, both 1's and 0's appear in the DISABLED field of my report. Am I doing something wrong?

For further detail into my setup, I have two different master files that pull in the same fields. In my FEX, I'm combining their output using MORE. Originally I had 9 flag fields and using AND statements in my WHERE clause, attempted to filter based on selection criteria from the user. After it failed to work I pared it down to just one, and am having the same issue.

 

[rasETL]

jcollier3

It has been a long time since I worked with FOCUS, but I see a couple of items you might want to change...

The field/flag: DISABLED is defined as A20 - if it is only going to be a 0 or 1, define it as A01 or Numeric.

Also the variable &DISABLED - you might try changing the variable to a global variable: &&DISABLED - note double ampersand.

Hope that helps.

 

[NollaunoUK]

jcollier3,

Also DISABLED might be a reserved word (need to check this).

DEFINE FILE <FILENAME> CLEAR
DIS_FLAG /I1 = IF ((UPCASE(NSACCOUNTLOCK) EQ 'TRUE') THEN 1 ELSE IF ((UPCASE(NSACCOUNTLOCK) EQ 'FALSE') THEN 0
ELSE 2;
END

TABLE FILE <FILENAME>


WHERE (DIS_FLAG EQ '&DIS_FLAG')
END

With this you can either get the program to ask you for the prompt or you can set a -DEFAULT for &DIS_FLAG set to 2 as default and the program that you get to run this will pick all records, unless you provide a variable when running the fex.