topic Re: Problem in Select Statment in Application Development and Automation Discussions

Problem in Select Statment

Former Member — Mon, 07 May 2007 06:17:11 GMT

I have two tables

TAB1

F1 F2

A1 AA

A2 AA

A3 BB

A4 AA

A5 AA

TAB2

F1 F2 F3

B1 AA D1

B2 BB D1

B3 CC D1

B4 AA D2

B5 AA D3

B6 BB D1

B7 AA D1

B8 DD D1

I use following statement

Select F1 from TAB1 where TAB1.F2 in (Select F2 from TAB2)

It’s working properly with my requirement.

My problem is now I want to get field F1 in TAB2 also. How can I get this?

Finally I needed result is

TAB1-F1 TAB2-F1

A1 B1

A2 B1

A3 B2

A4 B1

A5 B1

Basically I want, when TAB1-F2 data in TAB2-F2 field I want get TAB2-F1 field also.

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:20:23 GMT

Hi Nelson,

Use outer joins on two tables TAB1 and TAB2.

plz reward points if helpful or if it solves ur query.

Thanks

Chinmay

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:22:10 GMT

Hi,

SELECT S~F1 S~F2 P~F1
INTO   CORRESPONDING FIELDS OF TABLE IT_ITAB
FROM   TAB1 AS S
       INNER JOIN TAB2 AS P ON S~F2  =  P~F2 .

Regards

Sudheer

Re: Problem in Select Statment

former_member198270 — Mon, 07 May 2007 06:26:24 GMT

hi Nelson.

Syntax

... [(] {dbtab_left [AS tabalias_left]} | join

{[INNER] JOIN}|{LEFT [OUTER] JOIN}

{dbtab_right [AS tabalias_right] ON join_cond} [)] ... .

Effect

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 language elements may not be used: BETWEEN, 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.

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.

we r using 2 type of joins in abap they are

1) inner join.

this will join 2 tables using an common fiend and return the result with field values wich are common in both the tables

itab1 itab2

a b c a d

1 2 3 1 5

2 3 4 3 6

after innerjoining itab1 n itab2 on field a we wil get the o/p as

a b c d

1 2 3 5

only common field is taken..

2)left outer join

here it wil work in opossite way it will give values whic are not common

itab1 itab2

a b c a d

1 2 3 1 5

2 3 4 3 6

after left outer joining itab1 n itab2 on field a we wil get the o/p as

a b c d

2 3 4

only fields which are not common is taken from the left table..other field(d here) wil be empty

I think it will help u.

Reward Points if helpful.

regards,

Amber s

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:27:01 GMT

Hi nelso,

Try with this sample code in this u have to do only thing is to replace the fields with your required tables and use join condition and where condition like shown below it will work properly

SELECT  ekko~lifnr ekko~ebeln ekko~knumv ekko~bedat
          ekpo~werks ekpo~matnr ekpo~txz01 ekpo~werks
          ekpo~netwr
*          ekpo~aedat
          ekbe~ebelp  ekbe~belnr
          lfa1~name1
  INTO    CORRESPONDING   FIELDS OF  TABLE int_outtab

  FROM ( ( ( ekko

           JOIN ekbe ON  ekbe~ebeln = ekko~ebeln AND
                         ekbe~vgabe = '2'       )
           JOIN ekpo ON ekpo~ebeln = ekko~ebeln   AND
                        ekpo~ebelp = ekbe~ebelp )
           JOIN lfa1 ON lfa1~lifnr = ekko~lifnr )
  WHERE  ekko~lifnr  =  P_lifnr  AND
         ekko~ebeln IN  S_ebeln  AND
*         ekpo~werks IN  S_werks AND
         ekbe~vgabe = '2'.

Rewards if helpfull

Regards

Pavan

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:31:02 GMT

Hi,

Modify your query as this n try

Select TAB1_{F1 TAB2}F1

into table tab3

from TAB1

inner join TAB2

on TAB1_{F2 EQ TAB2}F2

for all entries in tab2

where tab1_{f2 eq tab2}f2.

Message was edited by:

Asha Raghuvanshi

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:40:09 GMT

dear all

i dont want to repeat TAB1-F1

i wanna just mapped 1 record from TAB2-F2

Re: Problem in Select Statment

Former Member — Mon, 07 May 2007 06:50:17 GMT

Ok then if u want map only one field u can use COMPARE statement or else u can use join condition as i hav sown above

Regards

Pavan