topic Re: Subquery in Open SQL in Application Development and Automation Discussions

Subquery in Open SQL

former_member185171 — Wed, 03 Dec 2014 15:04:28 GMT

Hi,

in native SQL and in SQL console in the HANA studio, one can write a subquery in the fieldlist of a select-statement:

select

it.fmbelnr,

...

pr.zztffc,

( select

sum (fkbtr)

from fmifiit where bukrs = it.bukrs

and kngjahr = it.kngjahr

and knbelnr = it.knbelnr

and knbuzei = it.knbuzei

and wrttp = '54' ) as som54 ,

( select

sum (fkbtr)

from fmifiit where bukrs = it.bukrs

and kngjahr = it.kngjahr

and knbelnr = it.knbelnr

and knbuzei = it.knbuzei

and wrttp = '57' ) as som57

from fmifiit as it

...

where ...

Is there a way to do this in ABAP - OPEN SQL?

Currently we are in release 7.40 SP04.

Thanks for the info.

Kris.

Re: Subquery in Open SQL

PeterJonker — Wed, 03 Dec 2014 15:06:57 GMT

Yes you can. I just did a search on SCN as well as ingoogle and there are many discussions etc about this. Please do a search before posting a question. Thanks.

Re: Subquery in Open SQL

former_member185171 — Wed, 03 Dec 2014 15:20:49 GMT

I did a lot of searches,

It's clear in the WHERE-condition. But I don't seem to find the subquery in the field-list of the select statement.

Re: Subquery in Open SQL

PeterJonker — Wed, 03 Dec 2014 15:22:21 GMT

OK, I am sorry. My apologies I didn't read your post correctly.

The answer is NO this can not be done in ABAP. In ABAP you can only have sub queries in the where condition, not in the field list.

Re: Subquery in Open SQL

ec1 — Thu, 04 Dec 2014 04:29:24 GMT

Hi Kris,

I just want to offer an alternative that can be implemented in Open SQL.

SELECT <any fields from T1> SUM ( T2~fkbtr) AS som54 SUM( T3~fkbtr ) AS som57

FROM fmifiit AS T1 LEFT OUTER JOIN fmifiit AS T2 ON

T2~bukrs = T1~bukrs

and T2~kngjahr = T1~kngjahr

and T2~knbelnr = T1~knbelnr

and T2~knbuzei = T1~knbuzei

and T2~wrttp = '54'

LEFT OUTER JOIN fmifiit AS T3 ON

T3~bukrs = T1~bukrs

and T3~kngjahr = T1~kngjahr

and T3~knbelnr = T1~knbelnr

and T3~knbuzei = T1~knbuzei

and T3~wrttp = '57'

WHERE ....

GROUP BY <any fields from T1>

Please note that LEFT OUTER JOIN and GROUP BY need to be used to get the same result.