topic Re: inner join with into corresponding in Application Development and Automation Discussions

inner join with into corresponding

Former Member — Mon, 08 May 2006 08:57:00 GMT

Hi,

can I use INNER JOIN in my select statement with INTO CORRESPONDING? If so, how to do it?

thanks so much!

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 08:58:05 GMT

select mara~matnr

marc~werks

marc~plnnr

marc~plnal from mara inner join marc

on mara_{matnr = marc}matnr

into CORRESPONDING FIELD OF table itab

where mara~matnr in s_mat

and marc~werks in p_plant.

but for increase performance avoid into corresponding field...

for that just arange the field in sequence in internal table as u fetch from select query

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 08:58:59 GMT

yes ucan use

select A~fld1
       B~fld2
       into corresponding fields of table itab
       from tab1 as A inner join
            tab2 as B on
            A~fld = B~fld.

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 09:00:18 GMT

Hello,

Here is an eg of inner join using three tables.

Try this :-

SELECT stpo_{stlnr stpo}idnrk mast_{matnr mara}mtart stpo~menge

INTO CORRESPONDING FIELDS OF TABLE zmat1 FROM mast

JOIN stpo ON stpo_{stlnr = mast}stlnr

JOIN mara ON mara_{matnr = mast}matnr

WHERE stpo_{stlty = 'M' "AND stpo}idnrk IN s_matnr

AND mast~werks = 1000.

Here s_matnr is a select-options on the selection-screen.

Thanks,

Susmitha

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 09:00:31 GMT

Try like this:

DATA: begin of wa,

DATE LIKE SFLIGHT-FLDATE,

CARRID LIKE SFLIGHT-CARRID,

CONNID LIKE SFLIGHT-CONNID.,

end of wa.

SELECT F_{CARRID F}CONNID F~FLDATE

INTO correspoding fields of wa

FROM SFLIGHT AS F INNER JOIN SPFLI AS P

ON F_{CARRID = P}CARRID AND

F_{CONNID = P}CONNID

WHERE P~CITYFROM = 'FRANKFURT'

AND P~CITYTO = 'NEW YORK'

AND F~FLDATE BETWEEN '20010910' AND '20010920'

AND F_{SEATSOCC < F}SEATSMAX.

WRITE: / wa-DATE, wa-CARRID, wa-CONNID.

ENDSELECT.

REgards,

Ravi

Re: inner join with into corresponding

vinod_gunaware2 — Mon, 08 May 2006 09:00:49 GMT

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>.

REPORT demo_select_inner_join.

DATA: BEGIN OF wa,

carrid TYPE spfli-carrid,

connid TYPE spfli-connid,

fldate TYPE sflight-fldate,

bookid TYPE sbook-bookid,

END OF wa,

itab LIKE SORTED TABLE OF wa

WITH UNIQUE KEY carrid connid fldate bookid.

SELECT p_{carrid p}connid f_{fldate b}bookid

INTO CORRESPONDING FIELDS OF TABLE itab

FROM ( ( spfli AS p

INNER JOIN sflight AS f ON p_{carrid = f}carrid AND

p_{connid = f}connid )

INNER JOIN sbook AS b ON b_{carrid = f}carrid AND

b_{connid = f}connid AND

b_{fldate = f}fldate )

WHERE p~cityfrom = 'FRANKFURT' AND

p~cityto = 'NEW YORK' AND

f_{seatsmax > f}seatsocc.

LOOP AT itab INTO wa.

AT NEW fldate.

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

ENDAT.

WRITE / wa-bookid.

ENDLOOP.

regards

vinod

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 09:02:18 GMT

Hi,

Use like this.

SELECT a_{process_type c}p_description_20

INTO CORRESPONDING FIELDS OF TABLE git_process_type

FROM crmc_pr_assign AS a

INNER JOIN crmc_proc_type AS b

ON a_{process_type = b}process_type

INNER JOIN crmc_proc_type_t AS c

ON b_{process_type = c}process_type

WHERE a~subobj_category = gc_subobject

AND b~process_blocked = space

AND c~langu = gc_langu.

<b>

Mark helpful answer.</b>

Re: inner join with into corresponding

Former Member — Mon, 08 May 2006 09:08:26 GMT

Hai Lana

Yes you can use Inner Join with the key word into corresponding Satement

Check the following Code

************************************************************************

Table Declaration *

************************************************************************

TABLES: mara.

************************************************************************

Types Declaration *

************************************************************************

TYPES: BEGIN OF typ_mara,

matnr TYPE mara-matnr, "Material Number"

mbrsh TYPE mara-mbrsh, "Industrial Sector"

mtart TYPE mara-mtart, "Material Type"

meins TYPE mara-meins, "Base Unit of Measure"

maktx TYPE makt-maktx, "Material Description"

END OF typ_mara.

************************************************************************

Intrnal tables Declaration *

************************************************************************

DATA: it_mara TYPE STANDARD TABLE OF typ_mara WITH HEADER LINE.

************************************************************************

Selection Screen *

************************************************************************

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

PARAMETERS : p_mtart LIKE mara-mtart.

SELECTION-SCREEN : END OF BLOCK b1.

************************************************************************

Start of Selection *

************************************************************************

START-OF-SELECTION.

***

SELECT A~matnr

A~mbrsh

A~mtart

A~meins

B~maktx

into corresponding fields of table it_mara

FROM mara as A inner join makt as B

on A_{matnr = B}mtnr

WHERE mtart = p_mtart.

if sy-subrc = 0.

sort it_mara by matnr.

else.

MESSAGE e001 WITH 'No data Found' ' For the Given'

'Selection Criteria'(400).

endif.

ENDIF.

Thanks & regards

Sreenivasulu P

Re: inner join with into corresponding

abdul_hakim — Mon, 08 May 2006 09:11:06 GMT

yes u can use it without any problem...

data: begin of itab occurs 0,

kunnr like kna1-kunnr

bukrs like knb1-bukrs,

end of itab.

select a_{kunnr b}bukrs into corresponding fields of table itab from kna1 as a inner join knb1 as b on

a_{kunnr = b}kunnr.

Cheers,

Abdul Hakim

Note:Reward Points for all useful answers...