Converting Native SQL to OPEN SQL

Former Member — Tue, 23 Jan 2007 15:25:34 GMT

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.

Re: Converting Native SQL to OPEN SQL

Former Member — Tue, 23 Jan 2007 15:41:17 GMT

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

Rob

Re: Converting Native SQL to OPEN SQL

Former Member — Tue, 23 Jan 2007 15:45:33 GMT

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

Thanks anyway

Re: Converting Native SQL to OPEN SQL

Former Member — Tue, 23 Jan 2007 15:52:11 GMT

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

topic Converting Native SQL to OPEN SQL in Application Development and Automation Discussions

Converting Native SQL to OPEN SQL

Re: Converting Native SQL to OPEN SQL

Re: Converting Native SQL to OPEN SQL

Re: Converting Native SQL to OPEN SQL