Hello,
I'm trying to set up a method for filtering many differnet reports to a particular set of site ids. The method I'd like to use is to have an include file set up a parameter that contains a set of site ids, separated by OR.
Then I want to include this file in other procedures and use the parameter in a WHERE statement. I can make it work with a single value, but when I try to add in the ORs then I don't get any records back. I think the problem might be whether to use or not use quotation marks at points in the code.

Here is how the parameter is set up in my include file:
-SET &ATSCLIENTS = '11' OR '166' OR '18';

Here is how I'm using it in the WHERE statement in another procedure:
WHERE Site_ID11 EQ &ATSCLIENTS;

Can anyone spot a problem here? Thanks!

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

Check your HTML source code when you run the procedure. We had a similar problem and for some reason, the html code was generating "NONE" instead of "OR". We had to use the STRREP function to fix the problem.

SET &FILTER1 = IF POSIT(&FILTER1,&FILTER1.LENGTH,' NONE ',6,'I2') NE 0 THEN STRREP(&FILTER1.LENGTH, &FILTER1, 6, ' NONE ', 4, ' OR ', &FILTER1.LENGTH, 'A&FILTER1.LENGTH') ELSE &FILTER1;  

Not sure if this is your problem, but what you explained sounded familiar to me.

If you require embeded quotes in a DM variable, you need to include extra ones:

-SET &CTRS = '''ENGLAND'' OR ''FRANCE'' OR ''JAPAN''';

TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY EQ &CTRS
END

A better alternative would be to put the values in a file, one per line:

FILEDEF CTRS1 DISK CTRS1.TXT
-RUN

-WRITE CTRS1 ENGLAND
-WRITE CTRS1 FRANCE
-WRITE CTRS1 CANADA
-WRITE CTRS1 JAPAN
-WRITE CTRS1 W GERMANY


TABLE FILE CAR
PRINT COUNTRY
WHERE COUNTRY IN FILE CTRS1
END

Thanks for the suggestion jseaburn. My difficultly was the more straighforward. I was missing the extra quotes suggested by Francis. Thanks for the tip, Francis. I'm still pretty GUI dependent but this will help me a lot in the future as well.

You can definitely generate code using the GUI that refers to a file for selection criteria, instead of a variable.

Looks like I was way off! Glad you were able to get the solution.

Just to throw my 2 cents in, I prefer to the the WHERE field IN (&FILTERLIST) format instead of using OR.

Thanks everyone. I've got the include file doing what I wanted. I also appreciate the alternative suggestions - but right now I'm sticking with the one process I was able to work out