topic Re: URGENTLY NEED INNER JOIN-SYNTAX in Application Development and Automation Discussions

URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:47:11 GMT

I URGENTLY NEED INNER-JOIN STATMENT SYNTAX

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:49:19 GMT

hi,

chk the foll code

PARAMETERS: p_cityfr TYPE spfli-cityfrom,

p_cityto TYPE spfli-cityto.

DATA: BEGIN OF wa,

fldate TYPE sflight-fldate,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa.

DATA itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY fldate carrname connid.

SELECT c_{carrname p}connid f~fldate

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( scarr AS c

INNER JOIN spfli AS p ON p_{carrid = c}carrid

AND p~cityfrom = p_cityfr

AND p~cityto = p_cityto )

INNER JOIN sflight AS f ON f_{carrid = p}carrid

AND f_{connid = p}connid ).

LOOP AT itab INTO wa.

WRITE: / wa-fldate, wa-carrname, wa-connid.

ENDLOOP.

regards,

Navneeth K.

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:50:58 GMT

Try as below

Select tab1_{field1 tab2}field2

into table t_table

from tab1

inner join tab2

on tab1_{field1 = tab2}field2

where field1 in s_field1.

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:52:57 GMT

Hi,

follow these link for sample programs and statement on INNER JOIN...

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb39c4358411d1829f0000e829fbfe/content.htm

http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp

regards,

Ashok Reddy

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:53:20 GMT

Hi Ashok,

Specifying Two or More Database Tables as an Inner Join

In a relational database, you normally need to read data simultaneously from more than one database table into an application program. You can read from more than one table in a single SELECT statement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

where <dbtab> is a single database table and <tab> is either a table or another join expression. The database tables can be specified statically or dynamically as described above. You may also use aliases. You can enclose each join expression in parentheses. The INNER addition is optional.

A join expression links each line of <tab> with the lines in <dbtab> that meet the condition <cond>. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

The syntax of the <cond> condition is like that of the WHERE clause, although individual comparisons can only be linked using AND. Furthermore, each comparison must contain a column from the right-hand table <dbtab>. It does not matter on which side of the comparison it occurs. For the column names in the comparison, you can use the same names that occur in the SELECT clause, to differentiate columns from different database tables that have the same names.

The comparisons in the condition <cond> can appear in the WHERE clause instead of the ON clause, since both clauses are applied equally to the temporary table containing all of the lines resulting from the join. However, each join must contain at least one comparison in the condition <cond>.

Specifying Two or More Database Tables as a Left Outer Join

In an inner join, a line from the left-hand database table or join is only included in the selection if there is one or more lines in the right-hand database table that meet the ON condition <cond>. The left outer join, on the other hand, reads lines from the left-hand database table or join even if there is no corresponding line in the right-hand table.

SELECT...

...

FROM <tab> LEFT [OUTER] JOIN <dbtab> [AS <alias>] ON <cond>

...

<tab> and <dbtab> are subject to the same rules and conditions as in an inner join. The OUTER addition is optional. The tables are linked in the same way as the inner join with the one exception that all lines selected from <tab> are included in the final selection. If <dbtab> does not contain any lines that meet the condition <cond>, the system includes a single line in the selection whose columns from <dbtab> are filled with null values.

In the left outer join, more restrictions apply to the condition <cond> than in the inner join. In addition to the above restrictions:

EQ or = is the only permitted relational operator.

There must be at least one comparison between columns from <tab> and <dbtab>.

The WHERE clause may not contain any comparisons with columns from <dbtab>. All comparisons using columns from <dbtab> must appear in the condition <cond>.

Minimize the Number of Data Transfers

In every Open SQL statement, data is transferred between the application server and the database system. Furthermore, the database system has to construct or reopen the appropriate administration data for each database access. You can therefore minimize the load on the network and the database system by minimizing the number of times you access the database.

Multiple Operations Instead of Single Operations

When you change data using INSERT, UPDATE, and DELETE, use internal tables instead of single entries. If you read data using SELECT, it is worth using multiple operations if you want to process the data more than once, other wise, a simple select loop is more efficient.

Avoid Repeated Access

As a rule you should read a given set of data once only in your program, and using a single access. Avoid accessing the same data more than once (for example, SELECT before an UPDATE).

Avoid Nested SELECT Loops

A simple SELECT loop is a single database access whose result is passed to the ABAP program line by line. Nested SELECT loops mean that the number of accesses in the inner loop is multiplied by the number of accesses in the outer loop. You should therefore only use nested SELECT loops if the selection in the outer loop contains very few lines.

However, using combinations of data from different database tables is more the rule than the exception in the relational data model. You can use the following techniques to avoid nested SELECT statements:

ABAP Dictionary Views

You can define joins between database tables statically and systemwide as views in the ABAP Dictionary. ABAP Dictionary views can be used by all ABAP programs. One of their advantages is that fields that are common to both tables (join fields) are only transferred once from the database to the application server.

Views in the ABAP Dictionary are implemented as inner joins. If the inner table contains no lines that correspond to lines in the outer table, no data is transferred. This is not always the desired result. For example, when you read data from a text table, you want to include lines in the selection even if the corresponding text does not exist in the required language. If you want to include all of the data from the outer table, you can program a left outer join in ABAP.

The links between the tables in the view are created and optimized by the database system. Like database tables, you can buffer views on the application server. The same buffering rules apply to views as to tables. In other words, it is most appropriate for views that you use mostly to read data. This reduces the network load and the amount of physical I/O in the database.

Joins in the FROM Clause

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

Subqueries in the WHERE and HAVING Clauses

Another way of accessing more than one database table in the same Open SQL statement is to use subqueries in the WHERE or HAVING clause. The data from a subquery is not transferred to the application server. Instead, it is used to evaluate conditions in the database system. This is a simple and effective way of programming complex database operations.

Using Internal Tables

It is also possible to avoid nested SELECT loops by placing the selection from the outer loop in an internal table and then running the inner selection once only using the FOR ALL ENTRIES addition. This technique stems from the time before joins were allowed in the FROM clause. On the other hand, it does prevent redundant data from being transferred from the database.

Using a Cursor to Read Data

A further method is to decouple the INTO clause from the SELECT statement by opening a cursor using OPEN CURSOR and reading data line by line using FETCH NEXT CURSOR. You must open a new cursor for each nested loop. In this case, you must ensure yourself that the correct lines are read from the database tables in the correct order. This usually requires a foreign key relationship between the database tables, and that they are sorted by the foreign key.

Reward pts if found usefull:)

Regards

Sathish

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:55:00 GMT

SYNTAX:

EXAMPLE OF INNER JOIN BETWEEN MARA & MAKT TABLE.

SELECT J1_{MATNR J1}MEINS J2~MAKTX

FROM MARA AS J1

INNER JOIN MAKT AS J2

ON J2_{MATNR EQ J1}MATNR

AND J2~SPRAS EQ 'E'

INTO CORRESPONDING FIELDS OF INT_TAB

WHERE J1~MATNR EQ MATERIAL_NO.

NOTE : AWARD POINTS IF USEFUL.....

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:56:18 GMT

SELECT

a~omeng

b~vgbel

b~vgpos

c~vbeln

c~posnr

c~klmeng

c~netwr

INTO CORRESPONDING FIELDS OF TABLE itab

FROM vbbe AS a INNER JOIN lips AS b

ON a_{vbeln = b}vbeln

AND a_{posnr = b}posnr

INNER JOIN vbap AS c

ON b_{vgbel = c}vbeln

AND b_{vgpos = c}posnr

WHERE a~vbtyp = 'J'

AND a~werks like '21%'

AND c~klmeng > 0.

I hope this will be helpful for u.

Message was edited by: anju

anju sinha

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 05:58:19 GMT

Hi Ashok,

When multiple SAP tables are logically joined, it is always advisable to use inner join to read the data from them. This certainly reduces the load on the network.

Let us take an example of 2 tables, zairln and zflight. The table zairln has the field airln, which is the airline code and the field lnnam, which is the name of the airline. The table zflight has the field airln, the airline code and other fields which hold the details of the flights that an airline operates.

Since these 2 tables a re logically joined by the airln field, it is advisable to use the inner join.

Select a_{airln a}lnnam b_{fligh b}cntry into table int_airdet

From zairln as a inner join zflight as b on a_{airln = b}airln.

In order to restrict the data as per the selection criteria, a where clause can be added to the above inner join.

pls Reward pts if found usefull

Regards

Sathish

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 06:43:08 GMT

I NEED SYNTAX FOR JOINING 4 TABLES AND USING A SELECT CONDITION ON IT

HELP ME

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 06:45:29 GMT

Hi ashok

Check this sample code

 Select single Vbrk~Bukrs Vbrk~Kunrg    Vbrk~Vbeln  
                   Vbrk~Fkdat Vbrk~Bstnk_Vf Vbrk~Zterm  
                   Tvzbt~Vtext  
                   Vbak~Vbeln Vbak~Bstdk  
                   Likp~Vbeln Likp~lfdat    Likp~Lfuhr  
       into w_vbrk  
       from vbrk  
      inner join       Tvzbt on Tvzbt~Zterm        = Vbrk~Zterm      and  
                                Tvzbt~Spras        = sy-langu  
      Inner join       Vbfa  as SalesLnk  
                             on SalesLnk~vbeln     = pu_vbeln        and  
                                SalesLnk~vbtyp_v   = c_order  
            inner join Vbak  on Vbak~Vbeln           = SalesLnk~Vbelv 
      Inner join       Vbfa  as DeliveryLnk  
                             on DeliveryLnk~vbeln   = pu_vbeln       and  
                                DeliveryLnk~vbtyp_v = c_Delivery  
            inner join Likp  on Likp~Vbeln          = DeliveryLnk~Vbelv  
      where vbrk~vbeln = pu_Vbeln.

Reward all helpfull answers

Regards

Pavan

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 06:52:50 GMT

WHILE WE ARE USING SELECT STATMENT WITH INNER-JOIN

SHOULD I CLOSE WITN END-SELECT

OR NOT

EXPLAIN ME

Re: URGENTLY NEED INNER JOIN-SYNTAX

Former Member — Mon, 16 Jul 2007 07:00:57 GMT

Hi Ashok

There is no need to declare a endselect statement. refer to the above example which i had given in which u can find 3 inner joins

CHECK THESE LINKS TOO

<a href="http://www.sap-img.com/abap/select-statement-with-inner-join-is-taking-forever.htm">INNER JOIN</a>

<a href="http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp">PROPER USE OF INNER JOIN</a>

Reward all helpfull answers

Regards

Pavan

Message was edited by:

Pavan praveen