topic Re: internal table in Application Development and Automation Discussions

internal table

Former Member — Thu, 14 Feb 2008 05:41:01 GMT

Hello Gurus,

How to combine data for 3 diff tables having same primary keys in to a resulant table ?

Thanks in advance,

Sriram.

Re: internal table

Former Member — Thu, 14 Feb 2008 05:49:11 GMT

Hi,

something like dis...........

loop at itab1.
move corresponding itab1 to itab_final.
read table itab2 with key fld1 = itab1-fld1. "fld1 is prim-ky
if sy-subrc is initial.
move corresponding itab2 to itab_final.
endif.
read table itab3 with key fld1 = itab1-fld1.
if sy-subrc is initial.
move corresponding itab3 to itab_final.
endif.
append itab_final.
endloop.

Cheers,

jose.

Re: internal table

Former Member — Thu, 14 Feb 2008 05:50:59 GMT

Hai.

You can use views and in where condition you use this primary key.

check the example.

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.

regards.

Sowjanya.b

Re: internal table

Former Member — Thu, 14 Feb 2008 05:51:02 GMT

hi,

just do like this,

first loop at itab1 into wa1.

move-corresponding wa1 to fwa. " final work area

read table itab2 into wa2 with key pkey = wa1-pkey.

if sy-subrc =0.

move-corresponding wa2 to fwa

endif.

read table itab3 into wa3 with key pkey = wa1-pkey.

if sy-subrc =0.

move-corresponding wa3 to fwa

endif.

append fwa to fitab. " final itab

endloop.

<REMOVED BY MODERATOR>

seshu.

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 5:11 PM

Re: internal table

Former Member — Thu, 14 Feb 2008 05:57:01 GMT

create ine Final ITAB conains all fields of ur 3 ITABS


Loop the 1st ITAB
  MOVE CORRESPONDING FIELDS OF ITAB1 to FINALITAB
  read table ITAB2 with key fld1= itab1-fld1
  MOVE CORRESPONDING FIELDS OF ITAB2 to FINALITAB

  read table ITAB3 with key fld1= itab1-fld1
  MOVE CORRESPONDING FIELDS OF ITAB3 to FINALITAB

append finalita
endloop

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 5:11 PM

Re: internal table

Former Member — Thu, 14 Feb 2008 05:57:20 GMT

suppose u hav 3 int tables : i1 i2 i3

and u want to combine them into final.

loop at internal table having maximum records.

first of al sort all the internal table based no the primary key.

sort i1 by fld1 fld2 fld3.

sort i2 by fld1 fld2 fld3.

sort i3 by fld1 fld2 fld3.

delete adjacent duplicates from i1.

delete adjacent duplicates from i2.

delete adjacent duplicates from i3.

loop at i1.

read table i2 with key fld1 = i1-fld1 fld2 = i1-fld2 fld3 = i1-fld3.

if sy-subrc = 0.

endif.

read table i3 with key fld1 = i1-fld1 fld2 = i1-fld2 fld3 = i1-fld3.

if sy-subrc = 0.

endif.

endloop.

Edited by: Kamini Rawat on Feb 14, 2008 6:59 AM

Re: internal table

Former Member — Thu, 14 Feb 2008 05:58:36 GMT

If your 3 tables are internal tables then you need to loop at the tables and for the common record(same primary keys) read the fields and populate the Target itab or Target Wa.

If your 3 tables are not internal tables and they are SAP/Z tables then you need to use Inner Join.

Re: internal table

Former Member — Thu, 14 Feb 2008 06:01:36 GMT

By making use of JOINS concepts we can combian two are more tables.There are 3 importent types of joins are there in SAP .

1 inner join 2 outer join 3 right outer join

you can use these joins depends on your requierment.

<REMOVED BY MODERATOR>

Edited by: Alvaro Tejada Galindo on Feb 14, 2008 5:12 PM