[SOLVED] Pass multi value parameters to sql stored proc
How do i pass multi value parameters chosen from a html launch page to a sql stored procedure?
Example: multi select list box COLOR
Blue Red Yellow (All 3 chosen from user)
PASSES INTO .FEX WHERE SQLPROC.ANSWER.INPUT EQ &COLOR.(OR()).COLOR.;
Need to pass into sql stored proc in conditional logic as:
SQL: WHERE (@Color = '' OR Color in(USER CHOSEN VALUES FROM LIST BOX)
IF there is any examples close to what i'm trying to do, please let me know
I have to pass multiple values from multiple list boxes to a drill down report that is populated by 1 sql stored proc. Quite new to this & there is not a lot of information on this.
THank uThis message has been edited. Last edited by: <Kathryn Henning>,
WebFOCUS 7.6 Windows, All Outputs
March 19, 2014, 03:42 PM
Danny-SRL
Nico,
First, welcome to FocalPoint. I don't know what type of launch page you wrote. However, I assume that you have a list box with multiple values. Let's take an example with the CAR file and a TABLE request:
TABLE FILE CAR
SUM
SALES
BY LOWEST COUNTRY
BY LOWEST SEATS
WHERE SEATS EQ &SEATS;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
Your list box, taking the values of SEATS from the file allows you to choose 2, 4, and 5. Say you chose 2 and 4. The HTML composer will then substitute &SEATS with the chosen values:
TABLE FILE CAR
SUM
SALES
BY LOWEST COUNTRY
BY LOWEST SEATS
WHERE SEATS EQ 2 OR 4;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
Now, what happens if you want to use IN instead of EQ:
TABLE FILE CAR
SUM
SALES
BY LOWEST COUNTRY
BY LOWEST SEATS
WHERE SEATS IN (&SEATS);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
The composer is still going to generate 2 OR 4. So, with a bit of dialog manager:
-SET &SLEN=&SEATS.LENGTH;
-SET &SEATS=STRREP(&SLEN, &SEATS, 2, 'OR', 1, ',', &SLEN, 'A&SLEN.EVAL');
TABLE FILE CAR
SUM
SALES
BY LOWEST COUNTRY
BY LOWEST SEATS
WHERE SEATS IN (&SEATS);
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
You can apply this to an SQL WHERE clause.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 20, 2014, 10:56 AM
nico
Hi:
Thank you for the quick response & your example is very helpful. However when i try to implement your example using multiple options, i am not able to get it to resolve. I AM able to choose 1 value OR All values, but not multiple values.
In my html page for my parameter i'm passing in, i have multiple options chosen in the properties window also, include quotes.
My where clause in my .fex is:
WHERE VALUE.VALUE.PARAMETER IN ('&PARAMETER);
When i implement my html page, i can choose 1 value only: Error message is:
'Value1' OR 'Value2' OR 'Value3' OR 'Value4' 0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC257) MISSING QUOTE MARKS: 'Value4' (FOC009) INCOMPLETE REQUEST STATEMENT BYPASSING TO END OF COMMAND
Please advise when u can Thank you very much.
WebFOCUS 7.6 Windows, All Outputs
March 20, 2014, 01:04 PM
njsden
I don't think you can mix the IN selector with OR operators. The following should give you a better chance:
WHERE VALUE.VALUE.PARAMETER EQ &PARAMETER;
Please note also that you probably won't need to enclose your variable in quotes as HTML Composer would be doing that for you, but testing is your best friend.
And nico, remove the quote before the amper : ('& in your WHERE clause.
WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF In Focus since 2007
March 20, 2014, 02:16 PM
nico
Thank you for your response. I was able to do a proof of concept & get multi value parameters to pass into a simple .fex based off a table with a simple where clause. However, I am stil not able to get them to pass into my sql stored proc.
AFter i modified my .fex logic to accept a simple .INPUT.@PARAMTERVALUE EQ &PARAMTERVALUE .(OR()).PARAMTERVALUE .;
In my .fex code, i can see this being passed correctly in: WHERE .INPUT.@PARAMTERVALUE EQ 'Value1' OR 'Value2';
However, i cannot get it to pass a option OR cannot get it to pass these values correctly to my sql stored proc: sql condition syntax is:
WHERE (@PARAMTERVALUE = 'All' OR ClaimTypeName in (@PARAMTERVALUE ))
ERROR MESSAGE @ BOTTOM OF .FEX:
(FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @PARAMTERVALUE
Please let me know ur thought when u get time. thanks
WebFOCUS 7.6 Windows, All Outputs
March 20, 2014, 04:45 PM
njsden
I missed the part in your original post about having to send this information to a stored procedure.
HTML Composer was designed to produce WebFOCUS-friendly code, not SQL passthru or other code.
In the presence of multi-value parameters, Composer will nicely give you a string of OR-separated values because that's how WebFOCUS handles it:
'value1' OR 'value2' OR 'value3'
No SQL engine I know of understands "OR" in that context, as they would expect a comma instead:
WHERE field IN ('value1','value2','value3')
Some Dialogue Manager can come in handy in that case so you can convert your " OR " values into ", " ones:
...
-* Convert ' OR ' to ', ' in the parameter list
-SET &PARAMTERVALUE = STRREP (&PARAMTERVALUE.LENGTH, &PARAMTERVALUE, 4, ' OR ', 2, ', ', &PARAMTERVALUE.LENGTH, 'A&PARAMTERVALUE.LENGTH');
...
-* Pass it to stored procedure / SQL passthru
WHERE VALUE.VALUE.PARAMETER IN (&PARAMTERVALUE);
...
Hi: Thank you for your reply. However, when i try to implement the suggestions above, i still get a error message when trying to pass multi values into my sql.
values being passed in: WHERE INPUT.@PARAMETERVALUE EQ 'Final', 'Final-Resubmit' ;
sql: where PARAMETERVALUE IN (@PARAMETERVALUE )
0 ERROR AT OR NEAR LINE 16 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC224) SYNTAX ERROR: , BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
Please let me know your thoughts when you can thank u very much.
WebFOCUS 7.6 Windows, All Outputs
March 24, 2014, 01:20 PM
Danny-SRL
Nico, Could you post your fex? It could help to see where the problem lies.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 24, 2014, 02:03 PM
nico
Hi:
I'm just trying to do a simple POC that this can actually be passed into my .sql, which i have not been able to do with multiple options, only single values OR all
My sql where clause is very simple: WHERE (@ClaimType = '' OR ClaimTypeName in (@ClaimType))
ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * INCLUDE = IBFS:/EDA/BIRS05/_EDAHOME/ETC/endeflt.sty, $ TYPE=REPORT, COLUMN=N4, WRAP=6.000000, $ TYPE=REPORT, COLUMN=N3, WRAP=6.000000, $ TYPE=REPORT, COLUMN=N2, WRAP=6.000000, $ ENDSTYLE END
ERROR MESSAGE ON HTML:
0 ERROR AT OR NEAR LINE 14 IN PROCEDURE ADHOCRQ FOCEXEC * (FOC224) SYNTAX ERROR: , BYPASSING TO END OF COMMAND (FOC009) INCOMPLETE REQUEST STATEMENT
WebFOCUS 7.6 Windows, All Outputs
March 24, 2014, 04:09 PM
Francis Mariani
As far as I can tell, you've built an HTML launch page using HTML Composer.
The parameter that the multi-select listbox is bound to can only have 'OR' or 'AND' as the separator property: "Options are Single select, Multiselect OR, and Multiselect AND" - documentation.
You need a comma separator. This is what I'd do - add Dialogue Manager code to replace the OR separator by comma:
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
March 24, 2014, 04:12 PM
Francis Mariani
I'm not sure what the .(OR()). does here, but perhaps it sets the parameter control to "OR"...
quote:
WHERE SQLPROC.ANSWER.INPUT EQ &COLOR.(OR()).COLOR.;
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
March 24, 2014, 04:55 PM
j.gross
quote:
In my .fex code, i can see this being passed correctly in: WHERE .INPUT.@PARAMTERVALUE EQ 'Value1' OR 'Value2';
... (FOC1631) MORE THAN ONE IF/WHERE TEST FOR FIELD @PARAMTERVALUE
If you are reporting using TABLE, against the synonym of a stored procedure, the procedure's parameters must be specified by a simple equality condition:
Syntax: How to Report Against a Stored Procedure Using the TABLE Command
To execute a stored procedure using the TABLE command, use the following syntax TABLE FILE synonym PRINT [parameter [parameter] ... | *] [IF in-parameter EQ value] . . . END
That rules out use of WHERE parameter IN( ... ) or IF parameter EQ value OR value ...
Hence the FOC1631.This message has been edited. Last edited by: j.gross,
March 25, 2014, 09:02 AM
nico
Thank you very much for all your help, i was able to resolve
WebFOCUS 7.6 Windows, All Outputs
March 25, 2014, 09:47 AM
Francis Mariani
You ought to tell us how you resolved this. Was it based on j.gross's information?
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
March 31, 2014, 09:41 AM
nico
No, i was not able to use the information provided by j.gross.
I was able to pass 2 values in using: -? & -SET &ECHO=ALL;
-* Convert ' OR ' to ', ' in the parameter list -SET &Parameter = STRREP (&Parameter .LENGTH, &Parameter , 4, ' OR ', 2, ', ', &Parameter .LENGTH, 'A&Parameter .LENGTH');
I am able to use a IN clasue in my sql
in my .fex i am able to use: @Parameter IN ('@Parameter ));
However, i have not been able to pass more then 2 values @ one time.
I keep gettin the message of: FOC1400 SQLCODE IS 201 (HEX: 00000C9) XOPEN:S0004 L (FOC1406) SQL OPEN CURRER ERROR.
IS this a bug? thanks
WebFOCUS 7.6 Windows, All Outputs
March 31, 2014, 09:58 AM
Kamesh
Which control you are using for the multiple values. If you are using TextArea in Composer page then there is a properties called "Multiple - set that to Multiple" and "Multiple:Add Quotes - set that to Yes"
That will allow you to send all the values to the report.
WFConsultant
WF 8105M on Win7/Tomcat
March 31, 2014, 12:40 PM
nico
I've tried to use both list box & check box with multiple values & quotes selected
Thanks
WebFOCUS 7.6 Windows, All Outputs
March 31, 2014, 12:57 PM
Francis Mariani
What does passing more than two values look like? If you have SET MSG=ON in your code, you should be able to see what your SQL statement looks like with the value of &Parameter embedded...
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
March 31, 2014, 01:01 PM
Francis Mariani
WebFOCUS error 1406:
(FOC1406) SQL OPEN CURSOR ERROR. %1%2%3%4 An error condition occurred while attempting to open a cursor in theRDBMS. An additional error message specifying the appropriate RDBMS error code will usually accompany this message. Consult the appropriate RDBMS error message reference for proper corrective measures.
Your request did not return any output to display. Possible causes: - No data rows matched the specified selection criteria. - Output was directed to a destination such as a file or printer. - An error occurred during the parsing or running of the request.
WHERE (DETAIL_TESTING.INPUT.@VALUE3 IN ('@VALUE3));
ON TABLE SET PAGE-NUM NOLEAD ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * -INCLUDE _EDAHOME/ETC/endeflt.sty
WebFOCUS 7.6 Windows, All Outputs
March 31, 2014, 03:58 PM
Francis Mariani
You should turn SQL traces on to see what gets generated by the TABLE FILE DETAIL_TESTING command, assuming DETAIL_TESTING is your stored procedure.
-*** Set up SQL tracing
-*** Deactivate SQL tracing
SET TRACEOFF = ALL
-*** Show Commands and data exchange between the physical and
-*** the logical layers of the data adapter
-*SET TRACEON = SQLCALL
-*** Enable Trace for the SQL Translator
SET TRACEON = SQLTRANS
-*** Show SQL statements
SET TRACEON = STMTRACE//CLIENT
-*** Show Optimization information
SET TRACEON = SQLAGGR//CLIENT
-*** Show SQL generated statement trace
-*SET TRACEON = STMTRACE/1/CLIENT
-*** Show SQL generated sub-statement trace
-*SET TRACEON = STMTRACE/2/CLIENT
-*** Disable the trace stamp (Date/Time etc)
SET TRACESTAMP = OFF
-*** Set trace line wrapping - # of characters
SET TRACEWRAP = 132
-*** Set trace to output file
-*SET TRACEUSER=C:\ibi\apps\baseapp\my_sql_trace.txt
-*** Activate SQL tracing
SET TRACEUSER = ON
-RUN
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
March 31, 2014, 05:05 PM
j.gross
quote:
Can u see what's wrong with my code
Yes, unbalanced quotes: WHERE (DETAIL_TESTING.INPUT.@VALUE3 IN ('@VALUE3'));
April 01, 2014, 07:53 AM
nico
Hi,
i missed the quote when i was trying to get my actual values out. There r quotes are in my .fex & I still cannot get this to work. When i was @ my focus user forum last week, they said this could be a bug & i'm starting to believe it could b. I can ONLY get multiple values to pass in when i use (IN LITERAL LIST) in painter, when i hard code the values, which i do not want to do. I need to get this to fuction using dynmaic .fex files that will grow once values are added to the dbase.
My sql where clause is this: WHERE (TriggerTypeName in (@TriggerTypeName))
My .fex with sql tracing is this:
Your request did not return any output to display. Possible causes: - No data rows matched the specified selection criteria. - Output was directed to a destination such as a file or printer. - An error occurred during the parsing or running of the request.
-SET &TRIGGERTYPENAME = STRREP (34, 'AcceleratedClaim' OR 'DeedInLieu', 4, ' OR ', 2, ', ', 34, 'A34'); TABLE FILE CWS3001L_TESTING PRINT CWS3001L_TESTING.ANSWERSET1.INVESTOR CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME CWS3001L_TESTING.ANSWERSET1.CLAIMID WHERE ( CWS3001L_TESTING.INPUT.@TRIGGERTYPENAME IN ('@TRIGGERTYPENAME') );
There are a couple of things that could be a problem here.
The first is that the incoming variable contains single quotes so in the STRREP I would be inclined to use .QUOTEDSTRING.
The second is that you are testing for the existence of @TRIGGERTYPENAME IN ('@TRIGGERTYPENAME'). This is testing for a string value of @TRIGGERTYPENAME as opposed to the variable you have so carefully coded.
I think your code should be -
-SET &TRIGGERTYPENAME = STRREP (&TRIGGERTYPENAME.LENGTH, &TRIGGERTYPENAME.QUOTEDSTRING, 4, ' OR ', 2, ', ', &TRIGGERTYPENAME.LENGTH, 'A&TRIGGERTYPENAME.LENGTH');
TABLE FILE CWS3001L_TESTING
PRINT CWS3001L_TESTING.ANSWERSET1.INVESTOR
CWS3001L_TESTING.ANSWERSET1.TRIGGERTYPENAME
CWS3001L_TESTING.ANSWERSET1.CLAIMID
WHERE CWS3001L_TESTING.ANSWERSET1.@TRIGGERTYPENAME IN (&TRIGGERTYPENAME);
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
Thank you very much for your help, i have implemented the suggestions, however i still cannot get this to resolve. I can see in my .fex that the values look as if they arein the correct format. This is my output as of now
Your request did not return any output to display. Possible causes: - No data rows matched the specified selection criteria. - Output was directed to a destination such as a file or printer. - An error occurred during the parsing or running of the request.