join statment in Native SQL

[Former Member]

Hello,

I am trying to fetch data from the database using EXEC SQL...ENDEXEC. statments.

i have succeeded fetching the data from one table but haven't ssucceeded from multiple number of tables

(i simply don't know the syntax).

Can you help me and give me an example (in <u>Native SQL</u>)?

Regards

[Former Member]

Hi,

EXEC SQL.

select count(*) into :lv_count from fin_partner a,

fin_partner_address b,

(select fp_code, fp_address from

fin_partner_address x

where exists (select 'x' from fin_partner_address

where fp_line_count > 2

and fp_code = x.fp_code) and fp_line_count = 2)

c where a.fp_code = b.fp_code

and b.fp_line_count = 1

and c.fp_code(+) = b.fp_code.

ENDEXEC.

Check this out. If this helps please don't forget to reward points.

Regards

Abhishek

[Former Member]

Hi,

Check this

<b>EXEC SQL.</b>

Addition

... PERFORMING form

Effect

Executes the Native SQL command enclosed by the statements EXEC SQL and ENDEXEC . In contrast to Open SQL , addressed database tables do not have to be known to the ABAP/4 Dictionary and the ABAP/4 program does not have to contain appropriate TABLES statements.

Example

Create the table AVERI_CLNT :

<b>

EXEC SQL.
  CREATE TABLE AVERI_CLNT (
         CLIENT   CHAR(3)  NOT NULL,
         ARG1     CHAR(3)  NOT NULL,
         ARG2     CHAR(3)  NOT NULL,
         FUNCTION CHAR(10) NOT NULL,
         PRIMARY KEY (CLIENT, ARG1, ARG2)
                          )
ENDEXEC.

</b>

With Native SQL commands, passing data between an ABAP/4 program and the database is achieved using host variables . A host variable is an ABAP/4 variable prefixed by a "*" in the Native SQL statement.

Example

Display a section of the table AVERI_CLNT :

DATA: F1(3), F2(3), F3(3).
F3 = ' 1 '
EXEC SQL.
  SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.
WRITE: / F1, F2.

To simplify the spelling of INTO lists in the SELECT command, you can specify a single structure as the target area as in Open SQL .

Example

Display a section of the table AVERI_CLNT :

DATA: BEGIN OF WA,
        CLIENT(3), ARG1(3), ARG2(3),
      END OF WA.
DATA  F3(3).
F3 = ' 1 '
EXEC SQL.
  SELECT CLIENT, ARG1 INTO :WA FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.
WRITE: / WA-CLIENT, WA-ARG1.

Notes

In contrast to Open SQL , a client field in Native SQL is a field like any other and must be specified explicitly in calls.

Authorization checks cannot be properly realized in EXEC SQL . You should perform these in the program.

When you start the R/3 System, a CONNECT to the current database is executed automatically. An explicit CONNECT is unnecessary.

A Native SQL command can (but does not have to) end with a ";". Under no circumstances should it end with a ".".

Some database systems allow upper and lower case in table names and field names. If you want to take advantage of this, you must ensure that the spelling of names is correct. To enable entry of lower case letters in names in the ABAP/4 editor, you must set the attribute for upper/lower case in the report.

Since there are no arrays in ABAP/4 , array operations are not possible in Native SQL . If the result of a SELECT command is a table, you can read this table line by line either with the Native SQL command FETCH or with the addition ... PERFORMING form .

Unlike in ABAP/4 programs, the character " in a Native SQL statement does not introduce a comment until the end of the editor line.

Addition

... PERFORMING form

Effect

If the result of a SELECT command is a table, you can read this table line by line in a processing loop. The subroutine form is called once for each line. In this subroutine, you can leave the loop by using EXIT FROM SQL . If the result of the selection is a single record, the subroutine is called only once.

Example

Display a section of the table AVERI_CLNT :

DATA: F1(3), F2(3), F3(3).

F3 = ' 1 '
EXEC SQL PERFORMING WRITE_AVERI_CLNT.
  SELECT CLIENT, ARG1 INTO :F1, :F2 FROM AVERI_CLNT
         WHERE ARG2 = :F3
ENDEXEC.

FORM WRITE_AVERI_CLNT.
  WRITE: / F1, F2.
ENDFORM.

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/exec.htm

Hope this helps.

Kindly reward poibnts and close thethread if it helps or get back.

[jayanthi_jayaraman]

Hi,

Try this.

EXEC SQL.

select empname, deptname into :f1 :f2 from

emp join dept on (emp.deptno = dept.deptno) where empname = 'Ganesh'

ENDEXEC.

The join query in oracle is

SELECT empname, deptname

FROM emp join dept on (emp.deptno = dept.deptno)

where empname = 'Ganesh'

Kindly reward points by clicking the star on the left of reply,if it is useful.