I am trying to setup the PROMPT and SET commands to determine if a record is active based on an existing or missing date field. The prompts look like this:

-PROMPT &ACTIVE.(YES,NO).

-SET &ACTIVE = IF &ACTIVE = 'YES' THEN 'IS MISSING' ELSE 'IS NOT MISSING';

The Where is:

WHERE (END_DATE '&ACTIVE')

I am trying to get the WHERE statement to be conditional based on the prompt.

try

IF &ACTIVE EQ .... in stead of "="

Frank

I made the correction but the results are the same. The error reads:

(FOC261) EXPRESSION IS INCOMPLETE BECAUSE AN OPERATION IS MISSING
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ON

The line the errors point to are the WHERE statement. If I enter a normal WHERE statement the program runs fine. Is there another way to do conditional branching in the WHERE statement?

What does the WHERE statement look like after variable substitution?

Have you SET &ECHO to ALL to look at the code?

quote:

-PROMPT &ACTIVE.(YES,NO).

-SET &ACTIVE = IF &ACTIVE = 'YES' THEN 'IS MISSING' ELSE 'IS NOT MISSING';

Guillo, your PROMPT is incorrect:

-PROMPT &ACTIVE.(<YES,1>,<NO,2>).

then:

-SET &ACTIVE = IF &ACTIVE EQ '1' THEN 'IS MISSING' ELSE 'IS NOT MISSING';

then

WHERE END_DATE '&ACTIVE';

WHERE statements should always end with a semi-colon.

You may want to use 2 & variables, ACTIVE1 for the PROMPT, &ACTIVE2, for the SET, just for clarity...

Tom

Edit: Dang Graemlins...

-PROMPT &ACTIVE.(YES,NO).

-SET &ACTIVE = IF &ACTIVE EQ 'YES' THEN 'IS MISSING' ELSE 'IS NOT MISSING';
.
.

WHERE (END_DATE &ACTIVE)

Just remove the single quotes from around &ACTIVE in the where, they surround the operator as well.

Prompt is fine like that, change the = as Frank said to EQ.

Thanks everyone for your help. Here's the results of the last 3 approaches.

Frances:
I could not figure out how to use the SET &ECHO command. Perhaps you could provide an example.

Tom:
This approach did not work when using the single quotes on '&ACTIVE'

Alan:
by suggesting to remove the single quotes this made Tom's approach work and my original (YES,NO) method work also. This was the key to the problem. I tried different SET options and they all worked.


Thank you all for your help.

-SET &ECHO=ALL;