topic Re: Join query in Application Development and Automation Discussions

Join query

Former Member — Thu, 12 Jul 2007 05:25:10 GMT

Hi all

anyone can send the syntax of join query

Re: Join query

Former Member — Thu, 12 Jul 2007 05:26:40 GMT

JOIN predicate

A JOIN predicate specifies a JOIN. A JOIN predicate can be specified with or without one or with two OUTER JOIN indicators.

Syntax

<join_predicate> ::=

<expression> [<outer_join_indicator>] <comp_op> <expression> [<outer_join_indicator>]

<outer_join_indicator> ::=

expression, comp_op

Explanation

Each expression must contain a column specification. A column specification must exist for the first and second expression so that both specifications refer to different table names or reference names.

Let x be the value of the first expression and y the value of the second expression. The values x and y must be comparable with one another.

The rules outlined under comparison predicate apply here.

If at least one OUTER JOIN indicator is specified in a JOIN predicate of a search condition, the corresponding table expression must be based on exactly two tables, or the following has to apply:

OUTER JOIN indicators are only specified for one of the tables in the FROM clause.

All of the JOIN predicates in this table to just one other table contain the OUTER JOIN indicator.

All other JOIN predicates contain no OUTER JOIN indicators.

If a JOIN requires more than two tables for the QUERY specification and if one of the rules above cannot be observed, a QUERY expression can also be used in the FROM clause.

Only those rows from the table that have a counterpart of the comparison operator in the JOIN predicate specified in the table are transferred to the result table.

The OUTER JOIN indicator must be specified on the side of the comparison operator where the other table is specified if each row in a table is to appear at least once in the result table.

If it is not possible to find at least one counterpart for a table row in the other table, this row is used to build a row for the result table. The NULL value is then used for the output columns which are formed from the columns in the other table.

Since the OUTER JOIN indicator can be specified on both sides of the comparison operator if the table expression is based on just two tables, it can be ensured that each line in both tables appears at least once in the result table.

The JOIN predicate is a special case of the comparison predicate. The number of JOIN predicates in a search condition is limited to 128.

JOIN predicate

Model tables customer, reservation

Is there a reservation for the customer 'Porter'? If so, for what date?

SELECT reservation.rno, customer.name, reservation.arrival, departure

FROM customer, reservation

WHERE customer.name = 'Porter' AND customer.cno = reservation.cno

RNO

NAME

ARRIVAL

DEPARTURE

100

Porter

13.11.1998

15.11.1998

110

Porter

24.12.1998

06.01.1999

Specifying an OUTER JOIN indicator

Model tables hotel, reservation

List all the hotels in Chicago for which a reservation exists and those for which a reservation does not exist. Missing reservation numbers are assigned a NULL value.

SELECT hotel.hno, hotel.name, reservation.rno

FROM hotel, reservation

WHERE hotel.city = 'Chicago' AND hotel.hno = reservation.hno

HNO

NAME

RNO

Eight Avenue

Lake Michigan

120

Midtown

100

Midtown

140

rewards point if useful....

ABhay.

Re: Join query

seshatalpasai_madala — Thu, 12 Jul 2007 05:26:44 GMT

Hi,

The join syntax represents a recursively nestable join expression. A join expression consists of a left-hand and a right- hand side, which are joined either by means of [INNER] JOIN or LEFT [OUTER] JOIN. Depending on the type of join, a join expression can be either an inner (INNER) or an outer (LEFT OUTER) join. Every join expression can be enclosed in round brackets. If a join expression is used, the SELECT command circumvents SAP buffering.

On the left-hand side, either a single database table, a view dbtab_left, or a join expression join can be specified. On the right-hand side, a single database table or a view dbtab_right as well as join conditions join_cond can be specified after ON. In this way, a maximum of 24 join expressions that join 25 database tables or views with each other can be specified after FROM.

AS can be used to specify an alternative table name tabalias for each of the specified database table names or for every view. A database table or a view can occur multiple times within a join expression and, in this case, have various alternative names.

The syntax of the join conditions join_cond is the same as that of the sql_cond conditions after the addition WHERE, with the following differences:

At least one comparison must be specified after ON.

Individual comparisons may be joined using AND only.

All comparisons must contain a column in the database table or the view dbtab_right on the right-hand side as an operand.

The following additions not be used: NOT, LIKE, IN.

No sub-queries may be used.

For outer joins, only equality comparisons (=, EQ) are possible.

If an outer join occurs after FROM, the join condition of every join expression must contain at least one comparison between columns on the left-hand and the right-hand side.

In outer joins, all comparisons that contain columns as operands in the database table or the view dbtab_right on the right-hand side must be specified in the corresponding join condition. In the WHERE condition of the same SELECT command, these columns are not allowed as operands.

Resulting set for inner join

The inner join joins the columns of every selected line on the left- hand side with the columns of all lines on the right-hand side that jointly fulfil the join_cond condition. A line in the resulting set is created for every such line on the right-hand side. The content of the column on the left-hand side may be duplicated in this case. If none of the lines on the right-hand side fulfils the join_cond condition, no line is created in the resulting set.

Resulting set for outer join

The outer join basically creates the same resulting set as the inner join, with the difference that at least one line is created in the resulting set for every selected line on the left-hand side, even if no line on the right-hand side fulfils the join_cond condition. The columns on the right-hand side that do not fulfil the join_cond condition are filled with null values.

Note

If the same column name occurs in several database tables in a join expression, they have to be identified in all remaining additions of the SELECT statement by using the column selector ~.

Example

Join the columns carrname, connid, fldate of the database tables scarr, spfli and sflight by means of two inner joins. A list is created of the flights from p_cityfr to p_cityto. Alternative names are used for every table.

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.

Example

Join the columns carrid, carrname and connid of the database tables scarr and spfli using an outer join. The column connid is set to the null value for all flights that do not fly from p_cityfr. This null value is then converted to the appropriate initial value when it is transferred to the assigned data object. The LOOP returns all airlines that do not fly from p_cityfr.

PARAMETERS p_cityfr TYPE spfli-cityfrom.

DATA: BEGIN OF wa,

carrid TYPE scarr-carrid,

carrname TYPE scarr-carrname,

connid TYPE spfli-connid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH NON-UNIQUE KEY carrid.

SELECT s_{carrid s}carrname p~connid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM scarr AS s

LEFT OUTER JOIN spfli AS p ON s_{carrid = p}carrid

AND p~cityfrom = p_cityfr.

LOOP AT itab INTO wa.

IF wa-connid = '0000'.

WRITE: / wa-carrid, wa-carrname.

ENDIF.

ENDLOOP.

Regards,

Sesh

Re: Join query

Former Member — Thu, 12 Jul 2007 05:27:38 GMT

hi,

check the foll code..

select mara~matnr

makt~maktx

into table itab

from mara inner join makt on mara_{matnr = makt}matnr.

regards,

Navneeth K.

Re: Join query

Former Member — Thu, 12 Jul 2007 05:27:49 GMT

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

rewards point if useful....

abhay.

Re: Join query

Former Member — Thu, 12 Jul 2007 05:27:50 GMT

Hi,

Check this:

http://help.sap.com/saphelp_46c/helpdata/EN/50/a71ec8f65911d296390000e82de14a/content.htm

Reward if useful!

Re: Join query

Former Member — Thu, 12 Jul 2007 05:29:12 GMT

Select statement with inner join is taking forever

Following is the select stmt which is taking forever.

SELECT MKPF_{BUDAT MKPF}CPUTM

MSEG_{MATNR MSEG}WERKS MSEG~EBELN

MSEG_{EBELP MSEG}ERFMG

INTO CORRESPONDING FIELDS OF TABLE

W_DTL_INVOICE

FROM MKPF INNER JOIN MSEG

ON MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR

WHERE MKPF~BUDAT > '20040721' AND

MSEG~BWART = '101' AND

MSEG~BUKRS = '1733'.

It does not have much records.

Can somebody let me know how to fine tune this statement.

Kasi

I have gone through the same problem last year. This is the solution I made. My program spended more than an hour to

select 5000 records from mkpf inner mseg(From a range of 100000 records) .

Now it needs less than 15 seconds to perform the same task.

Try this changes

in global data add these two hashed tables

data: begin of wa_mkpfmseg,

mblnr like mkpf-mblnr,

mjahr like mkpf-mjahr,

zeile like mseg-zeile,

bukrs like mseg-bukrs

bwart like mseg-bwart,

budat like mkpf-budat,

cputm like mkpf-cputm,

matnr like mseg-matnr,

werks like mseg-werks,

ebeln like mseg-ebeln,

ebelp like mseg-ebelp,

erfmg like mseg-erfmg,

end of wa_mkpfmseg.

data ht_mkpfmseg like hashed table of wa_mkpfmseg

with unique key mblnr mjahr zeile

with header line.

data: begin of wa_mkpfmsegSel,

budat like mkpf-budat,

cputm like mkpf-cputm,

matnr like mseg-matnr,

werks like mseg-werks,

ebeln like mseg-ebeln,

ebelp like mseg-ebelp,

erfmg like mseg-erfmg,

end of wa_mkpfmseg.

data ht_mkpfmsegSel like hashed table of wa_mkpfmsegSel

with unique key budat cputm matnr werks ebeln ebelp

with header line.

change your select sentence to look like this:

select mkpf~mblnr

mkpf~mjahr

mseg~zeile

mseg~bukrs

mseg~bwart

MKPF_{BUDAT MKPF}CPUTM

MSEG_{MATNR MSEG}WERKS MSEG~EBELN

MSEG_{EBELP MSEG}ERFMG

INTO TABLE ht_mkpfmseg

FROM MKPF INNER JOIN MSEG

ON mkpf_{mandt = mseg}mandt

AND MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR

where mkpf~budat > '20040721'.

loop at ht_mkpfmseg.

check ht_mkpfmseg-bukrs = '1733' and ht_mkpfmseg-bwart = '101'

read table ht_mkpfmsegsel with table key

budat = ht_mkpfmseg-budat

cputm = ht_mkpfmseg-cputm

matnr = ht_mkpfmseg-matnr

werks = ht_mkpfmseg-werks

ebeln = ht_mkpfmseg-ebeln

ebelp = ht_mkpfmseg-ebelp

transporting erfmg.

if sy-subrc <> 0.

move-corresponding ht_mkpfmseg to ht_mkpfmsegsel.

insert table ht_mkpfmsegsel.

else.

ht_mkpfmsegSel-budat = ht_mkpfmseg-budat.

ht_mkpfmsegSel-cputm = ht_mkpfmseg-cputm.

ht_mkpfmsegSel-matnr = ht_mkpfmseg-matnr,

ht_mkpfmsegSel-werks = ht_mkpfmseg-werks.

ht_mkpfmsegSel-ebeln = ht_mkpfmseg-ebeln.

ht_mkpfmsegSel-ebelp = ht_mkpfmseg-ebelp.

add ht_mkpfmseg-erfmg to ht_mkpfmsegSel-erfmg.

modify table ht_mkpfmsegSel transporting erfmg.

endif.

endloop.

" at this point ht_mkpfmsegSel has the data collected that you want.

loop at ht_mkpfmsegSel.

.... Here put the code between your select ... endselect.

endloop.

This should run faster because of the next reasons:

1. you're not distracting the sql optimizer when it analizes the sql where clause. It makes use of the first index declared

on mkpf (budat-mblnr)-

2. the data is selected into a hashed table which are the faster access tables provided by SAP from 4.0 up to 4.6c

(I don't know about 4.7)

3. As the select isn't restricting bukrs and bwart (but there is a good reason on this: it's faster to read 100000 records into

a hashed table and then filter the 80000 unwanted than to select the 20000 records via non index fields in the where clause.

I tested it in my own programs).

I hope this help you. If not, just let us know.

rewards point if useful......

Kind regards

abhay.

Re: Join query

Former Member — Thu, 12 Jul 2007 05:54:49 GMT

here is an example of inner join.

SELECT

a~omeng

b~vgbel

b~vgpos

c~vbeln

c~posnr

c~klmeng

c~netwr

INTO CORRESPONDING FIELDS OF TABLE it_tab

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.