I have a set statement with several codes that I want to exc...

[Howell Johnson 2]

I have a set statement with several codes that I want to exclude if I choose Y and include if I choose N. However, the report always excludes the codes regardless of choosing Y or N. The code I am using is below. It appears that my Where statement is not being populated with FOC_NONE.

-DEFAULTH &PMF = Y;

-SET &PMF1= IF &PMF EQ Y THEN M130 ELSE FOC_NONE;

-SET &PMF2= IF &PMF EQ Y THEN M131 ELSE FOC_NONE;

-SET &PMF3= IF &PMF EQ Y THEN M132 ELSE FOC_NONE;

-SET &PMF4= IF &PMF EQ Y THEN M135 ELSE FOC_NONE;

-SET &PMF5= IF &PMF EQ Y THEN M140 ELSE FOC_NONE;

WHERE SHIPTO_ADHOC_BV.COMMON_PRODUCT_ATTRIBUTES.SKU OMITS &PMF1 OR &PMF2 OR &PMF3 OR &PMF4 OR &PMF5;

[Michel Pageau]

Hi Howell,

FOC_NONE causes the line of code to be ignored, as if that line were blank.

The line need not be a WHERE phrase.

For example, it can be employed to eliminate a BY clause or a Verb Object.

_FOC_NULL forces a WHERE condition to evaluate as True, regardless of the incoming data values.

So, try with _FOC_NULL instead.

[Todd Wallace]

WHERE SHIPTO_ADHOC_BV.COMMON_PRODUCT_ATTRIBUTES.SKU OMITS &PMF1 OR &PMF2 OR &PMF3 OR &PMF4 OR &PMF5;

FOC_NONE might work if you broke it up line by line like

WHERE SHIPTO_ADHOC_BV.COMMON_PRODUCT_ATTRIBUTES.SKU OMITS &PMF1

OR &PMF2

OR &PMF3

OR &PMF4

OR &PMF5

;

As long as you always have that first value &PMF1. I have used this in other queries with the important part being that the FOC_NONE only eliminates items related to a single line(in this case the OR and variable). Note that the semicolon is on its own line.

[Martin Yergeau]

Since all your assignation depend on the same result (&PMF EQ Y), I suggest this instead:

-DEFAULTH &PMF = Y

-IF &PMF NE 'Y' THEN GOTO NOTEST;

WHERE NOT SHIPTO_ADHOC_BV.COMMON_PRODUCT_ATTRIBUTES.SKU IN ('M130', 'M131', 'M132', 'M135', 'M140');

-NOTEST

[Toby Mills]

Martins code reads the easiest for me, but Im old.

Let me ask the obvious question - are you sure &PMF is not set to Y (just use -SET &ECHO=ALL or -TYPE &PMF to check).

[Martin Yergeau]

toby.mills:

 

but Im old.

 

 

 

I think that most of the WF coders are old

[Howell Johnson 2]

Yes &PMF is set to Y. I tried using _FOC_NULL and that works. I also tested FOC_NONE and placed the &PMF on separate rows like in Todds example but that only works when I select Y When I select N the report doesnt pull any results. Martin your example looks interesting and I will definitely try this on future reports. Thanks to all for the quick response and help.

I agree I think most coders have been working with WF for a while.