| Creating a new condition using an IN predicate [message #324310] |
Sun, 01 June 2008 23:23  |
mcgirr
Messages: 4
Registered: June 2008
|
Junior Member |
|
|
Hi all,
I am new to BI using Discoverer so I hope the following questions I ask are not silly.
What I have done right now is created a new input parameter that maps to an integer column using the IN predicate condition and allowing multiple values.
Lets say the mapped column is user_id, therefore when I run the report I get a prompt where I can enter in 1,2,3 as the input values and the report will display where user_id is equal to 1 2 or 3.
What I am trying to achieve now is to store these multiple values into the database so that it is dynamically driven. So in a table I have a column of type varchar2 where I save this multiple values, e.g 1,2,3
Lets call this column ListOfUsers.
Now here is my problem. When I try to create a new condition where the column user_id appears in the ListOfUsers I get all sorts of problems. e.g. user_id IN ListOfUsers
First problem is a data type problem as it seems like Discoverer is having problems comparing numbers to strings, so in the condition window for the user_id column I cast it to a string by using either to_char or to_string.
This parses now but unfortunately the result set I am expecting comes back as empty now.
Can some one shed some light on how I can achieve this?
Thanks
Jim
|
|
|
|
| Re: Creating a new condition using an IN predicate [message #324339 is a reply to message #324310] |
Mon, 02 June 2008 01:32  |
mcgirr
Messages: 4
Registered: June 2008
|
Junior Member |
|
|
It looks like when I try to create the IN condition using input parameters or if I hard code the condition it would work as the Discoverer would parse the string correctly
e.g. if I entered 1,2,3,4,5 the SQL looks like this
(user_id IN (1,2,3,4,5)) which is spot on
BUT when I use a column value that contains the same string value 1,2,3,4,5 and cast them to the appropriate data type, it does not parse the column value as it would parse the manual input.
(user_id IN UserIDList) is what the SQL looks like and it is treating it as a plain string, not one that is meant to be used for a IN clause.
How can I work around this?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
