Symptoms:
****CASE 1 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (:myParameter)
- All does not work
- Single Selection is working
- Multiple Selection is working
***********CASE 2 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTSwhere DEPARTMENT_NAME IN (nvl(:myParameter, DEPARTMENT_NAME))
- All is working now
- Singe selection is working
- Multiple selection is not working any more (ORA-00909: Ungültige Anzahl von Argumenten / illegal amount of arguments)..
***********CASE 3 ************
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS where DEPARTMENT_NAME IN (:myParameter) or :myParameter IS NULL
- All is working
- Singe selection is working
- Multiple selection is not working (ORA-00920: Ungültiger relationaler Operator / illegal relational operator)
Solution:
select DEPARTMENT_ID as ID, DEPARTMENT_NAME as NAME from DEPARTMENTS
where ((coalesce(null, :Parameter) is null) or (DEPARTMENT_NAME in (:Parameter)))
14 comments:
Hi Jayu
Glad to find others blogging about publisher. Can I add you to my blog roll at the oracle publisher blog ?
http://blogs.oracle.com/xmlpublisher
Tim
Sure Tim. I've already seen my blog there and sorry for the late response.
Hi,
The solution u provided is good but does not work when multiple values are selected from the list.
Suppose, we select department as 'Account','HR', so the output should be based on 'Account' or 'HR' department.
But BI publisher treats this as a single string as "'Account','HR'" so none of the values are selected.
Please,Provide the solution for the same.
Adding to my previous comment....
This can be resolved by using the instr function. Following is the example
( coalesce( :Parameter, null) is null or instr(',' || :Parameter || ',' , ',' || DEPARTMENT_NAME || ',')>0
or instr(',' || :Parameter || ',' , ',''' || DEPARTMENT_NAME || ''',')>0)
Explaination of the conditions:
First we check whether the parameter contains any value or not with coalesce.
Second Condition : If the user selects a single parameter then the value will be received as "Account" (without quotes) then we insert a comma (before and after the value). So the value becomes (,Account,) and then we search the same value in the field with the help of instr function. Comma is put to ensure that instr does not for embedded seraching.
Last Condition : If the user selects the multiple values, say 'Account','HR' (values contains single quotes in case of multiple selection), then we insert a comma (before and after the value) in the parameter and while searching we put single quoted and comman in the field name.
Hi All,
My problem is also a similar case...I need to work out the query for all the 3 cases except for all, I need to pass -1...so that if I give -1 in the parameter the query should select all the values...
But when I use coalesce, in my case its not working out..feedback is appreciated...
to display all, please add the code in Template
It is a special case..
I want not acquiesce in on it. I assume warm-hearted post. Specially the title-deed attracted me to read the sound story.
Genial dispatch and this mail helped me alot in my college assignement. Say thank you you seeking your information.
Nice article as for me. It would be great to read more concerning this topic. Thanks for posting this material.
I really like when people are expressing their opinion and thought. So I like the way you are writing
Amiable brief and this enter helped me alot in my college assignement. Thank you seeking your information.
As a matter of a fact this is not news for me. But still thanks to the author.
I will add your site to favorites. Great work!
Post a Comment