SQL Subquery in FROM clause

[Former Member]

Hi,

some of my complex extractions that I am currently working on require nested SELECT statements. As far as I can see OpenSQL only supports subqueries in the WHERE clause, not in the FROM clause. Is this assumptions correct or is there a way around it? I have managed to use Native SQL for these nested expressions but the preference of the client is to use OpenSQL.

Any ideas?

Thanks,

Tobias

[Former Member · Accepted solution]

Hi

In from clause you can use join.

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

thanks

jagan

[Former Member]

Hi,

thanks for your replies.

The option of reading into an internal table has been considered, but because we are dealing with quite significant data volumes we are worried about memory. Something I should peruse further though.

The option of using joins would naturally be preferred. However, I have already explored this possibility and the calculations are just too complex. Joining data is fine - trying to aggregate at a particular level, then applying a condition (via HAVING clause) and then aggregating to a higher level again is just not possible with simple joins.

Cheers,

Tobias

[Rashid_Javed]

Well i don't think you can use subquery in FROM clause, however may be you can try to separate the subquery of FROM clause and instead use a variable placeholed in your from clause to replace tablename dynamically like

select * from (var_name) where ........

Hope this is helpful.