MULTI_COLUMN_IN_SUPPORTED ??

2981486

Hi,

I need to create a IN statement filter in my report with two columns.

Store Number, Product Number in ( 100, 2; 100, 3)

or even with a logical subquery

Store Number, Product Number in (select Store Number, Product Number from Sales where Amount > 10)

I found this in the RPD DB features, but I cant seem to figure it out how to use it... I always get a syntax error when trying to create the filter in the analysis:

MULTI_COLUMN_IN_SUPPORTED

Thank you,

Joao

Felipe_Idalgo

Hi,

Why do you need to use that?

You could use a simple filter like that:

         Store Number in (100, 2)

AND

         Product Number in (100, 3)

AND       

              Amount by Store > 10

         OR

              Amount by Product > 10

If you wanna to see all values in a single column you could use a multi-value column

Follow that if you really need, sincerly I would choose the first opetion (more simples)

OBIEE: Multiple column values in a single column

I hope this help

Felipe Idalgo

2981486

Thanks, but I need to filter both values at the same time.

In regular SQL, this would be something like:

Select * from table1 where (prod_num, store_num) in ((prod1,store1),(prod1,store2));

Note that this is different from the example you gave me:

Select * from table1 where prod_num in (prod1) AND store_num in (store1,store2);

Felipe_Idalgo

Hi,

Look that

I did a test here to explain other possibility

I created a table like that

create table teste_felipe (

store number,

prod number);

And then add some lines

insert into teste_felipe values (1,2);

insert into teste_felipe values (2,1);

insert into teste_felipe values (2,2);

insert into teste_felipe values (1,1);

My table data

Queries against this table

1) What you want

select * from teste_felipe where (store, prod) in ((2,2), (2,1))

2) My suggest

select * from teste_felipe where (store = 2 and prod = 2) or (store = 2 and prod = 1)

Both get the same result

You can make your filters using operators AND and OR on your criteria tab

I don't know your model but in my test the exec plan is similar

Felipe Idalgo

2981486

I can not have the values hard coded. I need to filters the values brought from a sub-query.

Store Number, Product Number in (select Store Number, Product Number from Sales where Amount > 10)

So your solution doesn't apply in my case..

Gianni Ceresa

A dirty (but really dirty) workaround could be to transform your 2 columns into a single one: concatenating Store Number with some random (impossible to have in your data) characters and than Product Number.

A more clean solution would be to adapt your model to be able to receive the "Amount > 10" condition and automatically make the required joins/filters on the 2 columns.

PS: was it clear that the first one is really DIRTY??? Try to go for the second one

Andrew Fomin.

I'm not sure that you can create a filter like this: "Store Number, Product Number in ( 100, 2; 100, 3)". But it's possible and easy to use a logical subquery in filters.

1. Create and test a subquery for a filter.

2. Create your main report.

3. Start adding a filter for any column.

4. Check "Convert this filter to SQL" tick box and press OK.

5. Write your multicolumn filter.

6. Enjoy.

Sample video based on a Sample Sales Lite demo: http://webmshare.com/play/m7qVG

And I believe you have some misinterpretation of the RPD DB features. It's not about turning them on or off. It's about telling OBIEE if the database can perform these features natively or not.

Manoj Dixit

Will filters based on another analysis work in your case?

Edit: If the attributes are based on a same dimension, I would think there won't be any issue in giving correct results. If they are from different dimensions, then based on fact table (of the base analysis), I think, they would form a correct combination as well, like Store and Product combo based on facts?

2981486

I thought about using concatenate to unite the two columns, but it is indeed an ugly solution.

Andrew, thats exactly what I need. I tried doing that using OBIEE 11.1.1.1.7 but I keep getting an error. Maybe it is a new feature for 12c?

Anyway, I will try it again, maybe I missed the parenthesis. Thanks!

Andrew Fomin.

It looks like you are right. Just checked this on 11.1.1.9. No luck.