Subquery in Open SQL

[former_member185171]

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.

[PeterJonker]

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.

[former_member185171]

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.

[PeterJonker]

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.

[ec1]

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.