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:
- SUBSELECT query does not support any WHERE condition except one condition of IN,
- 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.
