nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Help with a subquiry please

2850613

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.

Thanks for your help.

Bob

Find more posts tagged with

Accepted answers

All comments

SriniVEERAVALLI

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

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

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

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

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

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

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

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.

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:

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

Again thanks for the ideas!

Bob