I need an array or set-like variable to check membership of values. This seems simple enough but I have not found this information in the documentation, on forums and through web searches.

Example:

Instead of

WHERE MYTABLE.COUNTRY IN ('AU', 'NZ', 'TH', 'ZA');

I want to set up something re-usable like

-SET &COUNTRIES = ('AU', 'NZ', 'TH', 'ZA');

to use as

WHERE MYTABLE.COUNTRY IN &COUNTRIES;

I have tried various other approaches, including concatenated strings, without success.

How can this be done in WebFocus?

This message has been edited. Last edited by: FP Mod Chuck,

I'm not sure why you have to put this in a variable. Why not just do this:

 
TABLE FILE CAR
SUM SALES BY CAR
WHERE COUNTRY EQ 'ENGLAND' OR 'FRANCE';
END
 

If you're manufacturing the variable on the fly, try concatenating the values using ' OR '

-*
-SET &COUNTRIES = '(''ENGLAND'',''ITALY'')';
-*
TABLE FILE CAR
PRINT CAR
BY COUNTRY
WHERE COUNTRY IN &COUNTRIES
END

The issue is with the quotes. So doing it manually car be a pain.
The below do work

-SET &COUNTRIES = '(''ENGLAND'', ''FRANCE'', ''ITALY'')';
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY CAR
WHERE COUNTRY IN &COUNTRIES;
END

But how does &COUNTRIES is normally provided ? Is it coming from a selection made from a control in a HTML page ?

More than likely the last two posts will solve your issue, but you said you haven't had any success. With how you have it currently are you getting an error? Just curious on the details surrounding the issue.

We used to use IN() a lot.
Turns out that doesn't really work well with HTML parameters.
OR works better.

-SET &ARRAY = ' ''AU'' OR ''NZ'' OR ''TH'' OR ''ZA'' ';

TABLE FILE x
WHERE COUNTRYCODE EQ &ARRAY
END

is the easy way.

Thanks for all your answers. Both jfr99's/MartinY's and Dave's methods are working for me.

I should probably look more into how WebFocus treats quotes.

@MartinY/Dave - no HTML parameters involved, the list will be hardcoded.

@BabakNYC - the reason I want to use a variable is that my list of countries is actually much longer, used in several places and it may need updating from time to time. A variable will keep the code shorter and easier to maintain.

@jcannavo - the only error message I got with the original, incorrect syntax was "EDA no data".

Thanks again!

You can also put your values in a file and then use ...

WHERE COUNTRY IN FILE HLD_CNTRY
or
WHERE NOT COUNTRY IN FILE HLD_CNTRY

Here's an example ...

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
WHERE COUNTRY IN ('ENGLAND','ITALY')
ON TABLE SET PRINTONLY ON
ON TABLE HOLD AS HLD_CNTRY
END
-*
TABLE FILE HLD_CNTRY
HEADING
" VALUES IN HLD_CNTRY "
" "
PRINT *
END
-*
TABLE FILE CAR
HEADING
" WHERE COUNTRY IN FILE HLD_CNTRY "
" "
PRINT CAR
BY COUNTRY
WHERE COUNTRY IN FILE HLD_CNTRY
END
-*
TABLE FILE CAR
HEADING
" WHERE NOT COUNTRY IN FILE HLD_CNTRY "
" "
PRINT CAR
BY COUNTRY
WHERE NOT COUNTRY IN FILE HLD_CNTRY
END