topic Re: Query in native SQl in Application Development and Automation Discussions

Query in native SQl

Former Member — Wed, 16 Aug 2006 06:31:46 GMT

I'm new to Native SQL commands.

Can somebody tell me how to fine tune this code.

Is there anything like FOR ALL ENTRIES in these commands also. We're using Oracle database.

loop at it_cus1.

EXEC SQL PERFORMING APPEND_IT_CUS2.

SELECT SAP_MASTNR, ANK_MASTNR

INTO

:IT_CUS2-KUNNR, :IT_CUS2-DATLT

FROM NOVARTIS.TABLE_ADRESS@ANKD

WHERE ANK_MASTNR = :IT_CUS1-DATLT

ENDEXEC.

endloop.

FORM append_it_cus2 .

APPEND it_cus2.

CLEAR it_cus2.

ENDFORM.

Former Member — Wed, 16 Aug 2006 06:44:10 GMT

Hi Phani

   Not sure but have you tried using subquery if you are
 extracting it_cus1 using native sql.

Kind Regards
Eswar

Former Member — Wed, 16 Aug 2006 06:45:52 GMT

Hi Eswar problem is I'm getting this it_cus1 using Open SQL.

former_member186741 — Wed, 16 Aug 2006 06:51:46 GMT

DOES THE DATABASE HAVE AKEY OR INDEX ON 'ANK_MASTNR' ... if not it will be slow.

former_member186741 — Wed, 16 Aug 2006 23:34:59 GMT

you could try cursor processing, it might be more efficient:

EXEC SQL.

OPEN C FOR

SELECT SAP_MASTNR, ANK_MASTNR

FROM NOVARTIS.TABLE_ADRESS@ANKD

WHERE ANK_MASTNR = :IT_CUS1-DATLT

ENDEXEC.

loop at it_cus1.

EXEC SQL.

FETCH NEXT C into

:IT_CUS2-KUNNR, :IT_CUS2-DATLT

ENDEXEC.

APPEND it_cus2.

CLEAR it_cus2.

ENDloop.

EXEC SQL.

CLOSE C

ENDEXEC.