CODEWITHSUNDEEP
oracle-databaseabapopensql
frankie015
frankie015

Reputation: 358

How to recreate SAP queries in Oracle?

I need to recreate some SAP stored procedures in Oracle. I've been trying to find tutorials, similar questions, examples, etc about this but apparently no one had to do this before

What Oracle SQL query can be similar to this SAP query ?

SELECT * FROM A
 INTO CORRESPONDING FIELDS OF TABLE B
 FOR ALL ENTRIES IN C
 WHERE a = C-a
 AND x = y.

 LOOP AT B INTO D.
   D-b = E-b.

 INSERT c FROM D.
 IF SY-SUBRC <> 0.
   WRITE: / 'error on insert', D-b, D-a.
 ENDIF.

Any help will be appreciated, Thanks.

Upvotes: 4

Views: 849

Answers (2)

Nelson Miranda
Nelson Miranda

Reputation: 5554

I recommend you to use transaction 'ST05' to trace your program. This tool will show details of the queries on the database including the exact SQL executed.

EDIT: As a demonstration of the queries generated by SAP for Oracle let's execute this code and trace it with transaction 'ST05'. Remember to run 'ST05' before executing the program.

tables: mara.
data: it_mara type standard table of mara,
      it_eina type standard table of eina.

select-options so_matnr for mara-matnr.

start-of-selection.

select matnr from mara into corresponding fields of table it_mara 
up to 100 rows where matnr in so_matnr.

check sy-subrc eq 0.

select * from eina into table it_eina for all entries in it_mara
  where matnr eq it_mara-matnr.

After execution check the output in transaction 'ST05':

enter image description here

If you want more details select an SQL statement in the screen and then click the button 'Explain'. You will see the following:

enter image description here

For better reference on transaction 'ST05' check this link.

Hope it helps.

Upvotes: 4

Jagger
Jagger

Reputation: 10524

The FOR ALL ENTRIES statement usually produces many queries which results are then grouped by UNION or UNION ALL.

Here is a really nice analysis for Microsoft SQL Server.

Because of the fact that UNION and UNION ALL are part of SQL standard I think it is implemented exactly the same for any other SQL database.

[EDIT]

As Mr Miranda stated it looks differently when it comes to Oracle database. I googled a bit and found this article where it is said that IN-LISTs are used which seems also to be plausible.

Upvotes: 1

Related Questions