Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
MULTI_COLUMN_IN_SUPPORTED ??

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
Answers
-
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
0 -
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);
0 -
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
0 -
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..
0 -
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
0 -
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.
0 -
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?
0 -
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!
0 -
It looks like you are right. Just checked this on 11.1.1.9. No luck.
0