topic Re: Joining Tables in Application Development and Automation Discussions

Joining Tables

Former Member — Wed, 23 May 2007 14:09:04 GMT

I have the following code

select MANDT BUKRS BELNR BLART BLDAT BUDAT CPUDT AEDAT OBJECTCLAS USERNAME UDATE UTIME

From BKPF INNER JOIN CDHDR on OBJECTCLAS = MANDT

into corresponding fields of wa_BKPF.

and i am getting an error saying that MANDT doesnt exist in any of the tables but it does exist in BKPF.

BTW the join would bring back no results but its just a test.

Thanks in advance

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:12:33 GMT

Join should be this way....


select BKPF~MANDT BUKRS BELNR BLART BLDAT BUDAT CPUDT AEDAT OBJECTCLAS USERNAME UDATE UTIME
INTO TABLE T_DATA
From ( BKPF INNER JOIN CDHDR 
           on BKPF~MANDT = CDHDR~MANDT )

But you would need to include the Client Specified condition....And...Why use MANDT as the join key???

Greetings,

Blag.

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:16:57 GMT

I dont want to use MANDT it was just a test to see if i have my sintax was correct

thanks

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:23:33 GMT

> Hi

> I dont want to use MANDT it was just a test to see if

> i have my sintax was correct

> thanks

Ok -:) So you need to find the keys to join both tables and use the sintax that I posted -;)

Greetings,

Blag.

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:30:36 GMT

Hi Dunne,

In Joins, ON condition will be used to join the tables, generally it will have the common field between the tables which can join on tables.

Check this sample program.

TYPES : BEGIN OF int_table_type,

carrid LIKE sflight-carrid,

connid LIKE sflight-connid,

fldate LIKE sflight-fldate,

price LIKE sflight-price,

seatsmax LIKE sflight-seatsmax,

seatsocc LIKE sflight-seatsocc,

cityfrom LIKE spfli-cityfrom,

cityto LIKE spfli-cityto,

END OF int_table_type.

DATA: itab1 TYPE TABLE OF int_table_type WITH HEADER LINE.

<b>SELECT *

INTO CORRESPONDING

FIELDS OF TABLE itab1

FROM

sflight AS a1

INNER JOIN

spfli AS b1

ON a1_{carrid = b1}carrid

AND a1_{connid = b1}connid.</b>

LOOP AT itab1.

WRITE: / itab1-carrid,

itab1-connid,

itab1-fldate,

itab1-price,

itab1-seatsmax,

itab1-seatsocc,

itab1-cityfrom,

itab1-cityto.

ENDLOOP.

Thanks,

Vinay

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:33:05 GMT

Get the object class in TCDOB Table by using table name BKPF and enter object class in CDHDR ,you will get idea

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:34:31 GMT

The OBJECTCLAS field in CDHDR is a concatenation of the fields MANDT BUKRS BELNR GJAHR in BKPF.

Do you know how i make this join?

sorry if my questions are basic, i am brand new to this and this is my first program

Thanks for your help

Re: Joining Tables

Former Member — Wed, 23 May 2007 14:45:27 GMT

Mandt is the field which represents the table is client specific.

when even u r joining 2 tables no need to use mandt.

And u said objectcals is concatination of few fields in bkpf.

in this concept inner join or outer join never works.

There shoul b atleast one key field relating both the tables.

ex : take kna1 an vbak.

key field is kunnr.

like this u need to check the foreign key b/w the tables u want to join b4 appling joins concept on them.

feel free to ask for elobarated info , if u r still not sure.

Re: Joining Tables

Former Member — Thu, 24 May 2007 09:48:04 GMT

Thanks everyone for your help