2 Replies Latest reply: Jan 10, 2017 3:03 AM by Hakan Ronningberg RSS

    SAP Connector (6.x.x = latest) SubSelect does not support IN condition

    Atsushi Saijo

      Hi Gurus, there is an esoteric phenomena in SAP Connector. (latest version, 6.x.x).

      Whenever we use condition 'IN', OpenSQL query fails.

       

      It it observed that:

      1. SUBSELECT query does not support any WHERE condition except one condition of IN,
      2. SELECT in WHERE statement does not support IN (). It always must be EQ, NE, GT etc. 

      *This forces developer to code loop(s) in the conditions. But perhaps most developers would use SUBSELECT to BSEG and KONV only. Would you advise how you have workaround on this?

       

      [This statement passes Netweaver, but KSCHL EQ 'ZAAD' is ignored]

      SQL SUBSELECT KNUMV KPOSN KSCHL KAWRT KBETR KPEIN WAERS KWERT

        FROM KONV WHERE

        KNUMV IN (SELECT KNUMV FROM VBRK

        WHERE FKDAT EQ '20170105'

        AND VTWEG NE '10'

        AND BUKRS EQ 'ZADV'

        )

        AND KSCHL EQ 'ZAAD'

        ;

      [Does not work: note 2 conditions exists in IN]

      SQL SUBSELECT KNUMV KPOSN KSCHL KAWRT KBETR KPEIN WAERS KWERT

        FROM KONV WHERE

        KNUMV IN (SELECT KNUMV FROM VBRK

        WHERE FKDAT EQ '20170105'

        AND VTWEG NE '10'

        AND BUKRS IN ('ZADV','ZADQ')

        )

        AND KSCHL IN ('ZAAD','ZAAC')

      ;

      This query works: the single statement query within SubSelect

      SELECT KNUMV FROM VBRK

        WHERE FKDAT EQ '20170105'

        AND VTWEG NE '10'

        AND BUKRS IN ('ZADV','ZADQ')

       

      So it means we would need to use SUBSELECT always in a loop, where we execute it one by one: e.g. for entity ZADV, ZADQ, then for cost type ZAAD then ZAAC...... etc.

       

      That is fine as well to control segmented dataset bits by bits, but I wonder if it is ABAP specification or actually bug?

       

      I should appreciate for your feedback in advance.