Execute Subquery before main query

user12863665

hi , i would like to know how can i execute sub query before executing the main query. this is required because of some database issue.

My Current Main Query is like below

Select A,B from Subject area A where A in (Select X from Subject Area A where X in(100,200,300))

i want like below.

This sub query should execute before executing the main query and return value should be in main query- Select X from Subject Area A where X in(100,200,300) - returns - 100,200,300

Select A,B from Subject area A where A in(100,200,300);

can anyone suggest me how can i generate the query like above?

Gianni Ceresa

Hi,

Not sure to really understand what you want to do ...

You would like to execute your subquery, the SELECT X, store the result somewhere as values and then use these values as filter in your main query. And you don't want the query to be embedded inside the other, right?

Well ... you are trying to solve issues of your database in OBIEE, it's not the job of OBIEE to solve design or setting issues of the database.

The maximum you can do is to use a filter based on another analysis. It will not be executed before and only the values used as filters but with some luck you can maybe have it in a different WITH block and it will still work more or less.

That's the only way to have the subquery, but will probably not solve your database issues.

user12863665

Thanks gianni for the response. Currently i am applying filter based on the another analysis.

I agree this wont fix the database issue way its working.

I would like to know if there is any other way i can execute sub query like i mentioned aboce?

Gianni Ceresa

Honestly?

Really don't think so ...

That's not how OBIEE generate and process queries.

You have 2 situations: OBIEE manage to push down to the database everything and you end up with a single query, many WITH blocks but still a single query, or if you do things OBIEE can't push down to the database the BI Server will do part of it (like if using a source not supporting functions or joining 2 different sources).

But in no case it will retrieve the list of values and then physically pass these values to the second query. It will always use the reference to another query if pushing down to the database.

If you model your things to force OBIEE to do the work itself ... it's probably going to be slower than the issues your DB has with the subquery.

But this would be the only alternative crossing my mind to avoid the subquery, a bad alternative as the BI server isn't a database but the only one you can maybe try ... (doing it your model will be quite bad in the RPD, but you must source the 2 queries from different database to have the BI Server doing part of the work, so you must duplicate the database and connection pool to source the 2 queries one from the original one and one from the new duplicated one)

Robert Angel

You could use a row level variable populated via an init_block to place a pseudo security layer over your report, not what it is meant for, but it would achieve the desired effect.