Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Passing a parameter with multiple OR values
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Passing a parameter with multiple OR values
 Login/Join
 
Member
posted
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,


WebFOCUS 7701
Windows 2008
All Formats

 
Posts: 17 | Registered: August 05, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
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.


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
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


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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.


WebFOCUS 7701
Windows 2008
All Formats

 
Posts: 17 | Registered: August 05, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
You can definitely generate code using the GUI that refers to a file for selection criteria, instead of a variable.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Gold member
posted Hide Post
Looks like I was way off! Glad you were able to get the solution.


WebFOCUS 7.7.03
Linux / Universe Db
HTML/PDF/EXCEL/HTML Active
 
Posts: 90 | Registered: November 03, 2009Reply With QuoteReport This Post
Master
posted Hide Post
Just to throw my 2 cents in, I prefer to the the WHERE field IN (&FILTERLIST) format instead of using OR.


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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 Smiler


WebFOCUS 7701
Windows 2008
All Formats

 
Posts: 17 | Registered: August 05, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Passing a parameter with multiple OR values

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.