topic Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Tue, 08 May 2007 21:43:46 GMT

I am trying to join 3 tables kna1,vbak,vbap.

The select query is

select k~kunnr,

k~name1,

k~ort01,

k~land1,

v~vbeln,

v~erdat,

v~netwr,

a~posnr,

a~arktx,

a~werks,

into table t_orders from

(kna1 as k inner join vbak as v

on k_{kunnr = v}kunnr)

inner join vbap as a

on v_{vbeln = a}vbeln

where k~kunnr in s_custno

and v~vbeln in s_orderno.

And the display the data thru loop statement. The program is syntactically correct.

However execution lead to a runtime error.

Reason is " The data read during a select access could not be inserted into the target field.

Either conversion is not supported for the target's field type or the target field is too short to accept the value or the data or not in a form that the target field can accept."

How could i fix this error

Re: Inner Join

Former Member — Tue, 08 May 2007 21:45:51 GMT

There appears to be a problem with the definition of t_orders. Are all the fields you are selecting there in the proper order?

Rob

Re: Inner Join

Former Member — Tue, 08 May 2007 21:46:18 GMT

Hi,

Check the order of the fields declared in the internal table T_ORDERS and the order given in the select statement..

Both has to match..If you are using INTO TABLE t_orders..

Thanks,

Naren

Re: Inner Join

Former Member — Tue, 08 May 2007 21:47:45 GMT

Hi venkat,

The way you have defined t_orders shoukd be exactly in the same order as the order in the select statement.

the types and lengths of those fields also should be exactly the same as in the tables from which they are selected.

No commas should be there after each field in the select statement.

regards,

ravi

Re: Inner Join

Former Member — Tue, 08 May 2007 22:13:17 GMT

I am using end-of-page event and displaying page number. The report has 48 pages. It is displaying page no for 47 pages.

What should be done to display page number on the last page.

Re: Inner Join

Former Member — Tue, 08 May 2007 22:45:12 GMT

Your last page records are not completing the count required to populate end of page result.

check your list-count declarations and and try to add remaining blank or skip lines to reach it to the end of page.

Regards,

Amey

Re: Inner Join

Former Member — Wed, 09 May 2007 01:20:00 GMT

Hi Venkar ,

yes the line count for the end of page is not reaching if you difine the line count for the footer

then you skip the un occupied lines after writing then the last page will trigger

if you want to write the page of page numbers any one i can give u some simple code

simple go through it

FORM UPDATE_PAGE_NUMBERS.

***

DATA: TO_PAGE(6) TYPE C.

Get current number of pages.

TO_PAGE = SY-PAGNO.

Go to 1st line of every page and update 'Page n Of' Page number

DO SY-PAGNO TIMES.

READ LINE 1 OF PAGE SY-INDEX.

REPLACE '------' WITH TO_PAGE INTO SY-LISEL.

MODIFY CURRENT LINE.

ENDDO.

***

ENDFORM.

reward the points if usefull .

Cheers,

Srinu reddy.

Re: Inner Join

former_member196280 — Wed, 09 May 2007 03:14:54 GMT

Hi Venkat,

Try this. I guess this will help you

select k~kunnr,

k~name1,

k~ort01,

k~land1,

v~vbeln,

v~erdat,

v~netwr,

a~posnr,

a~arktx,

a~werks,

INTO CORRESPONDING FIELDS OF TABLE t_orders from

(kna1 as k inner join vbak as v

on k_{kunnr = v}kunnr)

inner join vbap as a

on v_{vbeln = a}vbeln

where k~kunnr in s_custno

and v~vbeln in s_orderno.

Reward point to all useful answers.

Regards,

SaiRam

Re: Inner Join

Former Member — Wed, 09 May 2007 03:33:25 GMT

Hi Venkat,

Check your select statement by given like this,

select k~kunnr,

k~name1,

k~ort01,

k~land1,

v~vbeln,

v~erdat,

v~netwr,

a~posnr,

a~arktx,

a~werks,

into corresponding fields of table t_orders from

( ( kna1 as k inner join vbak as v

on k_{kunnr = v}kunnr )

inner join vbap as a

on v_{vbeln = a}vbeln )

where k~kunnr in s_custno

and v~vbeln in s_orderno.

I think this going to be work.

Reward if it works.

Thanks.

Re: Inner Join

Former Member — Wed, 09 May 2007 05:21:11 GMT

REPORT ZRD_TEST no standard page heading line-size 250 line-count 60(10)

TABLES: vbak,kna1.

DATA: BEGIN OF t_table OCCURS 0,

kunnr LIKE kna1-kunnr,

name1 LIKE kna1-name1,

ort01 LIKE kna1-ort01,

land1 LIKE kna1-ort01,

vbeln LIKE vbak-vbeln,

erdat LIKE vbak-erdat,

netwr LIKE vbak-netwr,

posnr LIKE vbap-posnr,

arktx LIKE vbap-arktx,

werks LIKE vbap-werks,

END OF t_table.

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

SELECT-OPTIONS: s_kunnr for kna1-kunnr ,

s_vbeln for vbak-vbeln .

SELECTION-SCREEN END OF BLOCK b.

START-OF-SELECTION.

SELECT k_{kunnr k}name1 k_{ort01 k}land1 v_{vbeln v}erdat v~netwr

a_{posnr a}arktx a~werks INTO CORRESPONDING FIELDS OF TABLE t_table

FROM ( kna1 as k INNER JOIN vbak as v ON k_{kunnr = v}kunnr ) INNER JOIN

vbap as a on v_{vbeln = a}vbeln WHERE k_{kunnr in s_kunnr AND v}vbeln in

s_vbeln.

IF sy-subrc = 0.

SORT t_table BY kunnr vbeln.

ELSE.

MESSAGE i000(zsa) WITH 'NO DATA FOUND'.

ENDIF.

LOOP AT t_table.

WRITE:/ t_table-kunnr,

t_table-name1,

t_table-ort01,

t_table-land1,

t_table-vbeln,

t_table-erdat,

t_table-netwr,

t_table-posnr,

t_table-arktx,

t_table-werks.

ENDLOOP.

i think this code will help u

Re: Inner Join

former_member198270 — Wed, 09 May 2007 05:25:54 GMT

hi Venkat,

this may help you

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.

I u want the docs regarding that , let me know.

Reward Points if helpful.

Regards

Amber S

Re: Inner Join

Former Member — Wed, 09 May 2007 05:39:13 GMT

Hi,

This is surely problem with itab defination , t_orders :

this itab FIELD SEQUENCE and DATA TYPE must be like this:

k~kunnr, kna1

k~name1, kna1

k~ort01, kna1

k~land1, kna1

v~vbeln, vbak

v~erdat, vbak

v~netwr, vbak

a~posnr, vbap

a~arktx, vbap

a~werks, vbap

build itab like this and execute again.

Jogdand M B

Re: Inner Join

Former Member — Wed, 09 May 2007 05:54:28 GMT

hi,

just check out ur fields may be u might have given them in a wrong order....while declaring n while using in the select statement.....some times if the declaration of fields n the fields used in the select statement r not same then it shows this problem....

if useful reward with points,

with regards,

madhuri.