topic Re: Native SQL and FOR ALL ENTRIES statement in Application Development and Automation Discussions

Native SQL and FOR ALL ENTRIES statement

stefan_kolev4 — Fri, 21 Jan 2011 10:57:53 GMT

Hi,

I need to retrieve all records from external Oracle database using Native SQL for all records in an internal table in my program.

How can I do that ?

Cheers,

Stefan

Re: Native SQL and FOR ALL ENTRIES statement

Former Member — Fri, 21 Jan 2011 11:03:14 GMT

The first problem is to know if both db are linked by a dblink for example, then you can use the oracle native sql in your program see the help for EXEC SQL/ENDEXEC

But I don't think the option FOR ALL ENTRIES is supported by native sql: here you need to do a symple SELECT and load your internal table

Max

Re: Native SQL and FOR ALL ENTRIES statement

stefan_kolev4 — Fri, 21 Jan 2011 11:13:36 GMT

Hi,

Thanls for your fast responce.

Imagine we have an internal table in our program and we want to retrieve all equal records from external db using native sql.

Should I insert all records in an internal table and then use read table statement ?

Stefan

Re: Native SQL and FOR ALL ENTRIES statement

Former Member — Fri, 21 Jan 2011 11:19:16 GMT

Should I insert all records in an internal table and then use read table statement ?

That depends on how you need to arrange your program, I can't understand what " all equal records" means

Max

Re: Native SQL and FOR ALL ENTRIES statement

stefan_kolev4 — Fri, 21 Jan 2011 11:34:16 GMT

I am trying to do the same as SELECT....FOR ALL ENTRIES, but in Native SQL.

Stefan

Re: Native SQL and FOR ALL ENTRIES statement

Former Member — Fri, 21 Jan 2011 11:43:36 GMT

That mean you have an internal table with data from SAP and you need to load the data from extranal db in according to them?

If it's so I don't believe FOR ALL ENTRIES is supported and I don't know if there's a command like that, I think you have to upload record by record:

LOOP AT ITAB.
   EXEC SQL.
       SELECT * INTO :WA
            FROM <TABLE>
                  WHERE <FIELD> = :WA-FIELD.
     ENDEXC.
    IF SY-SUBRC = 0.
      APPEND WA TO ITAB2.
    ENDIF.
ENDLOOP.

mAX

Re: Native SQL and FOR ALL ENTRIES statement

Former Member — Tue, 23 Jun 2015 04:14:41 GMT

Hi Max,

Thanks for your reply.

I have one different requirement like can we load data from external DB based on internal table field condiition


LOOP AT ITAB.
   EXEC SQL.
       SELECT * INTO :WA
            FROM <TABLE>
                  WHERE <FIELD> = :WA-FIELD AND <FIELD1> = ITAB-FIELD1.
     ENDEXC.
    IF SY-SUBRC = 0.
      APPEND WA TO ITAB2.
    ENDIF.
ENDLOOP.

Please see the underlined where clause and suggest accordingly.

Thanks in advance.

-- MRM