Skip to Content
author's profile photo Former Member
Former Member

join statment in Native SQL

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Sep 27, 2005 at 11:04 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 27, 2005 at 11:04 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 27, 2005 at 12:04 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.