Nested subquery in FOR ALL ENTRIES

Question

Consultant sent me this code example, here is something he expects to get

SELECT m1~vbeln_im m1~vbelp_im m1~mblnr smbln      
    INTO CORRESPONDING FIELDS OF TABLE lt_mseg
    FROM mseg AS m1
    INNER JOIN mseg AS m2 ON m1~mblnr = m2~smbln
                         AND m1~mjahr = m2~sjahr
                         AND m1~zeile = m2~smblp
    FOR ALL ENTRIES IN lt_vbfa
    WHERE 
      AND m2~bwart = '102'
      AND 0 = ( select SUM( ( CASE
        when SHKZG = 'S' THEN 1
        when SHKZG = 'H' THEN -1
        else 0
        END ) *MENGE ) MENGE
        into lt_mseg-summ
        from mseg
        where
        VBELN_IM = m1~vbeln_im
        and VBELP_IM = m1~vbelp_im
        ).

The problem is I don't see how that should work in current syntax. I think about deriving internal select and using it as condition to main one, but is there a proper way to write this nested construction?

As i get it, if nested statement = 0, then main query executes. The problem here is the case inside nested statement. Is it even possible in ABAP? And in my opinion this check could be used outside from main SQL query.

Any suggestions are welcome.

Share(in steps): clearly explain, what is your intention + what problem are you facing + what have you done up till. — Asif Mehmood, Mar 22 '16 at 08:02
As i get it - if nested statement = 0, then main query executes, the problem here - case inside nested statement, is it even possible in ABAP? And logic dictates, that this check could be used outside from main SQL query. But i'm pretty novice to ABAP, so i leave a room for other possible explanations. — AlexanderK, Mar 22 '16 at 08:05
Edit your Question(Add this information to your POST), so that others could help! — Asif Mehmood, Mar 22 '16 at 08:09
The *Case* statement is possible but I see no sense in `FOR ALL ENTRIES` construction here. It is used for selection from DB table on the basis of **dependent** itab which your case lacks. I see no dependency between your `lt_vbfa` and `mseg` tables. You should clearly state your task. — Suncatcher, Mar 22 '16 at 08:47
And do not write code like this, just to show, that You can read and write ancient indian sanskrit coding ninja hieroglyphics. No one will be sad, if You split this monster select, and furthermore You also use for all entries, conditioned aggregation functions, ou ou ou . Way to heavy. It is right, that You pass the heavy work down to the db, ok, but even for this there are limitations, and You are already on the frontier. — icbytes, Mar 22 '16 at 08:52
Sorry for excessive quoting, the only part that really bother me - is nested query. With everything else - it's working fine. — AlexanderK, Mar 22 '16 at 09:15

score 2 · Answer 1 · answered Mar 26 '16 at 09:09

the logic that you were given is part of Native/Open SQL and has some shortcomings that you need to be aware of.

the statement you are showing has to be placed between EXEC SQL and ENDEXEC.
the logic is platform dependent.
there is no syntax checking performed between the EXEC and ENDEXEC
the execution of this bypasses the database buffering process, so its slower

To me, I would investigate a better way to capture the data that performs better outside of open/native sql.

If you want to move forward with this type of logic, below are a couple of links which should be helpful. There is an example select using a nested select with a case statement. Test Program Example Logic

score 0 · Answer 2 · answered Mar 23 '20 at 16:32

This is probably what you need, it works at least since ABAP 750.

SELECT vbeln UP TO 100 ROWS
 FROM vbfa
 INTO TABLE @DATA(lt_vbfa).

DATA(rt_vbeln) = VALUE range_vbeln_va_tab( FOR GROUPS val OF <line> IN lt_vbfa GROUP BY ( low = <line>-vbeln ) WITHOUT MEMBERS ( sign = 'I' option = 'EQ' low = val-low ) ).

SELECT m1~vbeln_im, m1~vbelp_im, m1~mblnr, m2~smbln
  INTO TABLE @DATA(lt_mseg)
  FROM mseg AS m1
  JOIN mseg AS m2
    ON m1~mblnr = m2~smbln
   AND m1~mjahr = m2~sjahr
   AND m1~zeile = m2~smblp
 WHERE m2~bwart = '102'
   AND m1~vbeln_im IN ( SELECT vbelv FROM vbfa WHERE vbelv IN @rt_vbeln  )
 GROUP BY m1~vbeln_im, m1~vbelp_im, m1~mblnr, m2~smbln
HAVING SUM( CASE  m1~shkzg WHEN 'H' THEN 1  WHEN 'S' THEN -1 ELSE 0 END * m1~menge ) = 0.

Yes, aggregating and FOR ALL ENTRIES is impossible in one SELECT, but you can trick the system with range and subquery. Also you don't need three joins for summarizing reversed docs, your SUM subquery is redundant here.

If you need to select documents not only by delivery number but also by position this will be more complicated for sure.

Nested subquery in FOR ALL ENTRIES

2 Answers2