Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

MULTI_COLUMN_IN_SUPPORTED ??

Received Response
44
Views
9
Comments
2981486
2981486 Rank 3 - Community Apprentice
edited Aug 13, 2024 6:20PM in Oracle Analytics Cloud and Server

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

  • Felipe_Idalgo
    Felipe_Idalgo Rank 5 - Community Champion

    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
    2981486 Rank 3 - Community Apprentice

    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
    Felipe_Idalgo Rank 5 - Community Champion

    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

    table_data.png

    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

    table_result.png

    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
    2981486 Rank 3 - Community Apprentice

    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
    Gianni Ceresa Mod

    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.
    Andrew Fomin. Rank 6 - Analytics Lead

    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
    Manoj Dixit Rank 6 - Analytics Lead

    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
    2981486 Rank 3 - Community Apprentice

    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.
    Andrew Fomin. Rank 6 - Analytics Lead

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