Help with a subquiry please — Oracle Analytics

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

Help with a subquiry please

Received Response
54
Views
8
Comments
2850613
2850613 Rank 1 - Community Starter
edited Aug 13, 2024 7:05PM in Oracle Analytics Cloud and Server

I am trying to create a sub-query. Below is a partial list of account numbers. Accounts are comprised of a 6 digit account, and a 2 digit sub account.  What I need to do is exclude complete accounts that that end in '99'. I will also need to exclude accounts that end in '00' and long there was a corresponding '99'.

For example, I would need to remove 910620-99 and 910620-00, however, I would not need to remove accounts such as 910035-00 or 910040-00.

Accounts.JPG

Thanks for your help.

Bob

Answers

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    Use filter

    is Not Like

    ex:

    ("Time"."Year" NOT LIKE '%5' AND "Time"."Year" NOT LIKE '%0')

    in your case that would something like

    (ColumnName NOT LIKE '%99' AND ColumnName NOT LIKE '%00')

  • Simon.D
    Simon.D Rank 4 - Community Specialist

    Perhaps you could have another sub-query? The first query would have a column with the first 6 digits (using a string function like ltrim or something) for all accounts that end 99. The second query would then use the first one as a filter, removing the account numbers that begin with the results of the first query.

    There might be a way of doing this within a single analysis but ... it's the weekend and I cant think of it right now

  • Jagadekara
    Jagadekara Rank 3 - Community Apprentice

    Hi,

    Not clear.

    You want to remove 910620-00,  but you need 910035-00. So how? based on which scenario you need second?

    Always better to provide table creation and insert scripts and required output for quick solutions...

  • Simon.D
    Simon.D Rank 4 - Community Specialist

    I think Bob is saying that any account (the number that comes before the hyphen) that has a sub-account (number that comes after the hyphen) of "99" then that account needs to be removed from the list.

  • Jagadekara
    Jagadekara Rank 3 - Community Apprentice

    Hi,

    Just check this... it may help you...

    SELECT SUBSTR(COL2,1,6), COL2, SUBSTR(COL2,8,9) FROM TEST2

    where substr(col2,1,6) not in (select substr(col2,1,6) from test2 where substr(col2,8,9)='99')

    ;

    replace your table and your column in above select statement

  • Rahul Motaparthi
    Rahul Motaparthi Rank 1 - Community Starter

    Hi Bob,

    Try the below sql.

    select * from
user_table
where user_column not like '%-99'
and  user_column not in (select replace(user_column,'-99','-00') from user_table where user_column like '%-99' );

    Thanks,

    Rahul.

  • Simon.D
    Simon.D Rank 4 - Community Specialist

    I'm assuming that, since Bob is in the OBIEE forums, that Bob is looking for a solution that doesn't require Bob to write SQL. Is that right Bob?

    Bob.

  • 2850613
    2850613 Rank 1 - Community Starter

    Thank you! This was a huge help. What I did was break the account down into 6 digits and 2 digits.

    Then, I did a sub-query that listed all accounts ending with '99'. This also had the first 6 digit account.

    pastedImage_0.png

    From this sub query, I did another sub query based upon the 6 digit account list in the first sub-query and filtered with 00 and 99. The produced an analysis of accounts that I wanted excluded from the original analysis.

    Exert:

    pastedImage_1.png

    Then, I just excluded the accounts listed in the second sub-query from the original analysis.

    Again thanks for the ideas!

    Bob