Home » Developer & Programmer » Reports & Discoverer » Creating a new condition using an IN predicate (Discoverer 10.1.2.1)
Creating a new condition using an IN predicate [message #324310] Sun, 01 June 2008 23:23 Go to next message
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 Go to previous messageGo to next message
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?
Re: Creating a new condition using an IN predicate [message #324562 is a reply to message #324339] Tue, 03 June 2008 02:42 Go to previous message
wency
Messages: 450
Registered: April 2006
Location: Philippines
Senior Member

i'm not sure, but it can be done on string, don't know how to deal with numbers. I suggest you to try and post the problem in PL/SQL experts.
Previous Topic: Flex field in Oracle Reports
Next Topic: Dynamically change the color in the report
Goto Forum:
  


Current Time: Mon Dec 05 13:00:24 CST 2016

Total time taken to generate the page: 0.09355 seconds