Execute Subquery before main query

Question

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?

Robert Angel · Answer

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.

Gianni Ceresa · Answer

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)

user12863665 · Answer

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?