topic Re: Native SQL Select data into a internal table..!! in Application Development and Automation Discussions

Native SQL Select data into a internal table..!!

Former Member — Tue, 09 Mar 2010 13:00:26 GMT

Hii All,

I want to use Native SQL in retrieving data from a standard table into a internal table.Here is the code which i have written.

DATA: it_cdhdr TYPE STANDARD TABLE OF cdhdr.

DATA: wa_cdhdr LIKE LINE OF it_cdhdr.

EXEC SQL PERFORMING append_itab.

SELECT *

INTO :wa_cdhdr

FROM cdhdr

ENDEXEC.

FORM append_itab.

APPEND wa_cdhdr TO it_cdhdr.

CLEAR wa_cdhdr.

ENDFORM.

This gives me a wrong number of entries in my internal table during the Debug. Dont know whats the reason.

Also, i want to know is there any other better way of accessing the database tabel and pass it into a inetrnal table?

Best Regards,

Srikanth

Re: Native SQL Select data into a internal table..!!

SuhaSaha — Tue, 09 Mar 2010 13:23:56 GMT


> Also, i want to know is there any other better way of accessing the database tabel and pass it into a inetrnal table?

Hello,

Use Open SQL. I do not see the point of using Native SQL ?

BR,

Suhas

Re: Native SQL Select data into a internal table..!!

former_member404244 — Tue, 09 Mar 2010 13:25:42 GMT

Hi,

Can you please tell us why you have used native sql.. You can very well use Open SQL....

Regards,

Nagaraj

Re: Native SQL Select data into a internal table..!!

Former Member — Tue, 09 Mar 2010 13:31:24 GMT

 //REPORT demo_native_sql.

DATA: BEGIN OF wa,
        connid   TYPE spfli-connid,
        cityfrom TYPE spfli-cityfrom,
        cityto   TYPE spfli-cityto,
      END OF wa.

DATA c1 TYPE spfli-carrid VALUE 'LH'.

EXEC SQL PERFORMING loop_output.
  SELECT connid, cityfrom, cityto
  INTO   :wa
  FROM   spfli
  WHERE  carrid = :c1
ENDEXEC.

FORM loop_output.
  WRITE: / wa-connid, wa-cityfrom, wa-cityto.
ENDFORM.

you are missing the field sequence in ur select ..

check this example for ur query

.. but before that try to answer the questions that are asked ..

Br,

Vijay.

Re: Native SQL Select data into a internal table..!!

Former Member — Tue, 09 Mar 2010 14:23:44 GMT

Native sql is used to select data from tables outside SAP like Oracle database system. In this scenario there is no use of native SQL,you are able to use Open SQL which is the correct way in this mathod.Revert back in case of any doubt.

Re: Native SQL Select data into a internal table..!!

Former Member — Tue, 09 Mar 2010 14:53:41 GMT

You want the entire content of CDHDR? Your program is going to run a while! Seriously, use Open SQL with the appropriate WHERE clause!

Re: Native SQL Select data into a internal table..!!

ThomasZloch — Tue, 09 Mar 2010 15:06:33 GMT

I guess there will be a memory problem before it can run too long

Thomas

Re: Native SQL Select data into a internal table..!!

Former Member — Tue, 09 Mar 2010 15:15:36 GMT

Hii Everybody,

Thank you so much for the answers.

Actually, we were asked by our team lead to use Native SQL rather than an direct Open SQL select.

Also, the example which i have given is for CDHDR table, but in the real scenario it is a Control table which has very few records.(max of 20 records)

For improving the performance we are asked to use a direct SQL statement rather than the normal way.

Please let me know if you need any other info. Help me out in writing a sql query such that i can directly insert data into a internal table.

Thank you.

Best Regards,

Srikanth

Re: Native SQL Select data into a internal table..!!

ThomasZloch — Tue, 09 Mar 2010 15:23:50 GMT

You want to improve the performance for reading a table with 20 entries? How often is it being read? Better use SAP table buffering!

Sorry, we must keep trying to steer you and your team lead into other territory. Avoid Native SQL in ABAP as much as you can.

Thomas

Re: Native SQL Select data into a internal table..!!

HermannGahm — Tue, 09 Mar 2010 15:43:22 GMT

Hi,


> This gives me a wrong number of entries in my internal table during the Debug. Dont know whats the reason.
>

client dependency? OPEN SQL is client dependent native SQL istn't (default)

Kind regards,

Hermann

Re: Native SQL Select data into a internal table..!!

HermannGahm — Tue, 09 Mar 2010 15:46:47 GMT

Hi,


> For improving the performance we are asked to use a direct SQL statement rather than the normal way. 
> 
> Please let me know if you need any other info. Help me out in writing a sql query such that i can directly insert data into a internal table.
>

you should not use native SQL for performance reasons in such a case. Actually OPEN SQL could be faster because it can use the SAP table buffer or use array interfaces which is not possible with native SQL. (Native SQL should be used for tables which can't be accessed with OPEN SQL or if you want to use a database specific SQL feature which is not available in OPEN SQL but for sure not in your case).

Kind regards,

Hermann