topic sub queries in Application Development and Automation Discussions

sub queries

Former Member — Mon, 17 Mar 2008 12:23:33 GMT

How to use netive SQL sub queries in ABAP?? Give some example.....

Re: sub queries

Former Member — Mon, 17 Mar 2008 12:31:35 GMT

Hi,

To use a Native SQL statement, you must precede it with the EXEC SQL statement, and follow it with the ENDEXEC statement as follows:

EXEC SQL [PERFORMING <form>].

<Native SQL statement>

ENDEXEC.

There is no period after Native SQL statements. Furthermore, using inverted commas (") or an asterisk (*) at the beginning of a line in a native SQL statement does not introduce a comment as it would in normal ABAP syntax. You need to know whether table and field names are case-sensitive in your chosen database.

In Native SQL statements, the data is transported between the database table and the ABAP program using host variables. These are declared in the ABAP program, and preceded in the Native SQL statement by a colon (:). You can use elementary structures as host variables. Exceptionally, structures in an INTO clause are treated as though all of their fields were listed individually.

If the selection in a Native SQL SELECT statement is a table, you can pass it to ABAP line by line using the PERFORMING addition. The program calls a subroutine <form> for each line read. You can process the data further within the subroutine.

Please reward points if useful.

Regards

Rose

Re: sub queries

dhruv_shah3 — Mon, 17 Mar 2008 12:32:17 GMT

Hi,

Use the following example

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.

The output is as follows:

0400 FRANKFURT NEW YORK

2402 FRANKFURT BERLIN

0402 FRANKFURT NEW YORK

The work area wa and the field c1 Native SQL statement SELECT are used. wa is the target area into which the selected data is written. As a structure, wa is handled in the INTO clause as if all subfields were listed individually: [..] INTO :wa-connid, :wa-cityfrom, :wa-cityto. c1 is used in the WHERE clause. The subroutine loop_output writes the data from wa to the list.

Also look at this link,

[Native SQL|http://help.sap.com/saphelp_nw04s/helpdata/en/fc/eb3b8b358411d1829f0000e829fbfe/frameset.htm]

HTH

Regards,

Dhruv Shah