Converting Native SQL to OPEN SQL

[Former Member]

Any inputs on how the following Native SQL can be converted to OPEN SQL.

Especially self join of same table .

EXEC SQL PERFORMING APPEND_ITAB.

SELECT /+ ORDERED/

A.VTYPE, A.LIFNR, G.MTNO,

A.ASYTR, A.DATAB, A.DATBI,

D.MAKTX, F.NAME1, G.COMP,

C.PREF, C.COMP, E.MAKTX,

C.QNTY, C.UNIT, B.MEINS,

A.EKGRP, A.SEQNO, C.STGB,

C.DATUV, C.DATUB

INTO :WA_MODULE-VTYPE, :WA_MODULE-LIFNR, :WA_MODULE-MATNR,

:WA_MODULE-ASYTR, :WA_MODULE-DATAB, :WA_MODULE-DATBI,

:WA_MODULE-MAKTX, :WA_MODULE-NAME1, :WA_SUB-UPGVC,

:WA_SUB-PREF, :WA_SUB-COMP, :WA_SUB-MAKTX,

:WA_SUB-QNTY, :WA_SUB-UNIT, :WA_SUB-MEINS,

:WA_MODULE-EKGRP, :WA_MODULE-SEQNO, :WA_SUB-STGB,

:WA_SUB-DATAB, :WA_SUB-DATBI

FROM ZTMM_ASSY_COST1 A, ZTBM_ABXDULDT G,

ZTBM_ABXDULDT C, MARA B, MAKT D, MAKT E, LFA1 F

WHERE A.MANDT = :SY-MANDT

AND A.VTYPE BETWEEN :WA_VTYPE_F AND :WA_VTYPE_T

AND A.MCODE BETWEEN :WA_MCODE_F AND :WA_MCODE_T

AND A.LIFNR BETWEEN :WA_LIFNR_F AND :WA_LIFNR_T

AND A.EKGRP BETWEEN :WA_EKGRP_F AND :WA_EKGRP_T

AND A.DATAB <= :P_DATUM

AND A.DATBI >= :P_DATUM

AND F.MANDT = A.MANDT

AND F.LIFNR = A.LIFNR

AND G.MANDT = A.MANDT

AND G.MTNO LIKE CONCAT(CONCAT(A.VTYPE,A.MCODE),'%')

AND G.MTNO BETWEEN :WA_MATNR_F AND :WA_MATNR_T

AND G.PLNT = :C_WERKS

AND G.USAG = '2'

AND G.ALTN in ('1','01')

AND G.DATUV <= :P_DATUM

AND G.DATUB >= :P_DATUM

AND B.MANDT(+) = G.MANDT

AND B.MATNR(+) = G.MTNO

AND B.LVORM(+) = ' '

AND C.MANDT(+) = G.MANDT

AND C.MTNO(+) = G.COMP

AND C.PLNT(+) = G.PLNT

AND C.USAG(+) = '2'

AND C.ALTN(+) = '01'

AND C.DATUV(+) <= :P_DATUM

AND C.DATUB(+) >= :P_DATUM

AND D.MANDT(+) = B.MANDT

AND D.MATNR(+) = B.MATNR

AND D.SPRAS(+) = :SY-LANGU

AND E.MANDT(+) = C.MANDT

AND E.MATNR(+) = C.COMP

AND E.SPRAS(+) = :SY-LANGU

ORDER BY A.VTYPE, A.LIFNR, G.MTNO

ENDEXEC.

[Former Member]

Why don't you give it a try yourself and come back to the forum if you have problems.

Rob

[Former Member]

I have already tryed and it is working. i thought i can get better inputs. Nothing else.

Thanks anyway

[Former Member]

Then why don't you post the code you have. It would be a lot easier for us to improve that rather than try do redo what you have already done.

Rob