I am trying to create a dynamic WHERE COUNTRY EQ statement, and I am not getting quite the result I am looking for:

quote:

-*
-SET &PARAMETER = 'ENGLAND';
-*
-SET &ALPHA = 'WHERE';
-SET &BETA = 'COUNTRY';
-SET &COCO= 'EQ';
-SET &ZPARM = '''' || '&' | PARAMETER || '''';
-*
-SET &NOQTS = &ALPHA||' '|&BETA||' '|&COCO||' '|&PARAMETER||';';
-TYPE &NOQTS
-*EXIT
-*
TABLE FILE CAR
PRINT
CAR
MODEL
BODYTYPE
BY COUNTRY
&NOQTS
END
-RUN

Here is the error result:

quote:

TABLE FILE CAR
PRINT
CAR
MODEL
BODYTYPE
BY COUNTRY
WHERE COUNTRY EQ ENGLAND;
END
-RUN
0 ERROR AT OR NEAR LINE 20 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: ENGLAND
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

I need the WHERE statement to be:
WHERE COUNTRY EQ 'ENGLAND';

Can anyone help me?

Thanks!

This message has been edited. Last edited by: Kerry,

Close! If you do a -SET &ECHO=ALL; at the beginning of your procedure and then run it, you would see the following for your &NOQTS line:

-SET &NOQTS = WHERE|' '|COUNTRY|' '|EQ|' '|ENGLAND|';';

which results in your where statement:

WHERE COUNTRY EQ ENGLAND;

Since there are no quotes around ENGLAND, WF thinks it is a field name. If you change your -SET to the following, you'll get what you need.

-SET &NOQTS = &ALPHA||' '|&BETA||' '|&COCO||' '|'''&PARAMETER.EVAL'''||';';

Note the .EVAL operator which is required to for immediate evaluation. Otherwise you get WHERE COUNTRY EQ '&PARAMETER';

The problem is with the missing quotes as Darin says. Here is one more solution -

  
-SET &QT = '''' ;

-SET &NOQTS = &ALPHA||' '|&BETA||' '|&COCO||' '| &QT || &PARAMETER|| &QT|| ';';

Sandeep Mamidenna

Here is another solution:

  
-*
-SET &PARAMETER = 'ENGLAND';
-*
-SET &ALPHA = 'WHERE';
-SET &BETA = 'COUNTRY';
-SET &COCO= 'EQ';
-SET &ZPARM = '''' || '&' | PARAMETER || '''';
-*
-SET &NOQTS = '&ALPHA.EVAL &BETA.EVAL &COCO.EVAL ''&PARAMETER.EVAL''';

-TYPE &NOQTS 
-*EXIT
-*
TABLE FILE CAR
PRINT 
CAR
MODEL
BODYTYPE
BY COUNTRY
&NOQTS.EVAL
END
-RUN

cheat
use IF
APP PATH IBISAMP
TABLE FILE CAR
PRINT *
IF COUNTRY IS ENGLAND
END
.. so
-SET &MYCOUNTRY = 'ENGLAND';
TABLE FILE CAR PRINT *
IF COUNTRY IS &MYCOUNTRY
END
..
IF expects a constant, a value, eg a number or a character
The parm alone won't work with 'W GERMANY'
but you know that already
but with single value paramters, (numbers or codes), this is the easy way.

Thanks everyone for your wide array of replies.
I never would of the approach used by Glenda:

quote:

-SET &NOQTS = '&ALPHA.EVAL &BETA.EVAL &COCO.EVAL ''&PARAMETER.EVAL''';

TABLE FILE CAR
PRINT
CAR
MODEL
BODYTYPE
BY COUNTRY
&NOQTS.EVAL
END
-RUN

How about

-SET &ThisCountry = 'ENGLAND' ;
-SET &MyWhere = 'WHERE COUNTRY EQ ' | '''' || &ThisCountry || '''' ;
TABLE FILE CAR
PRINT CAR MODEL MPG
&MyWhere.EVAL
END

NOTE the use of the four single quotes.

hmmm
Alpha Beta and Coco
weren't they the real names of the Supremes?

Why don't you do it the really easy way and just code:

-SET &PARAMETER = 'ENGLAND';
-SET &MYWHERE='WHERE COUNTRY EQ ''&PARAMETER'';';
TABLE FILE CAR
PRINT 
CAR
MODEL
BODYTYPE
BY COUNTRY
&MYWHERE.EVAL
END
  

I like that method as well. Less |'s. No need for the semi-colon. So how's this:

-SET &MYWHERE='WHERE COUNTRY EQ ''&PARAMETER''' ;

Which equates to

WHERE COUNTRY EQ 'ENGLAND'

"&" appearing within quotes in the left hand side of a -SET statement is treated literally. As it stands, that -SET treats &PARAMETER as a constant, not a reference (so you'd wind up fishing for "&PARAMETER", not "ENGLAND").

You need to append .EVAL to make it work.

-SET &MYWHERE= 'WHERE COUNTRY EQ ''&PARAMETER.EVAL'';' ;
...

Weird. But: Duh (on me). I knew that. I, sometimes, over use the ".EVAL" and forgot to include it here where it should be... But, it did work. The "WHERE COUNTRY EQ 'ENGLAND'" is the result of the "-SET &ECHO = ALL;"...Thanks Jack.

AND,

If IBI ever tightens the code, a semi-colon is required with a WHERE statement, FYI...

Tom

Now, Tom, Why would they do a thing like that? LOL