topic Re: Join in Application Development and Automation Discussions

Join

Former Member — Thu, 06 Sep 2007 12:19:10 GMT

can anyone please tell me

shall we can use join in select statement for 4 tables.

for eg

i have 4 database tables BKPF,BSEG,SKAT,LFA1

I need to write select statement for above tables using join, is it possible

please help me.

Re: Join

Former Member — Thu, 06 Sep 2007 12:21:25 GMT

hi,

yes u can use but its not recommended better u create an view and use it...if u use join ur perfomance will be reducd.

reward if useful

ravi

Re: Join

rodrigo_paisante3 — Thu, 06 Sep 2007 12:22:20 GMT

Hi, try to see the link between the tables, see this link:

Regards

Reward points if useful!

Re: Join

Former Member — Thu, 06 Sep 2007 12:33:33 GMT

Better option i feel is join bseg and bkpf. Get data to internal table. For this records get data from SKAT & LFA1. This will have better performance than joining all 4 tables. Let me know if you need exact join fields and related psudocode.

Award points, if you find this solution helpful.

Re: Join

Former Member — Thu, 06 Sep 2007 12:40:28 GMT

hi,

if u want to join more than 3 tables than go for views or use FOR ALL ENTRIES than joins as joins degrades performance of the program for more tables.

i think for ur purpose just create a database view and use in ur program.

if helpful reward some points.

with regards,

Suresh Aluri.

Re: Join

Former Member — Thu, 06 Sep 2007 12:45:10 GMT

Hi Hemal,

You can use join statement to join at max four tables.

For your requirement you can do that.

Refer this pseudo code :

SELECT A~<Field Name>

A~<Field Name>

B~<Field Name>

C~<Field Name>

D~<Field Name>

INTO CORRESPONDING FIELDS OF TABLE I_TAB

FROM BKPF AS A INNER JOIN

BSEG AS B ON A_{<Key Field> = B}<Key Field>

SKAT AS C ON A_{<Key Field> = C}<Key Field>

LFA1 AS D ON A_{<Key Field> = D}<Key Field>

WHERE <Condition>.

Refer this two table joining code for joining four tables.

SELECT A~VBELN

A~FKDAT

A~KUNAG

A~KUNRG

B~NAME1

B~ORT01 INTO CORRESPONDING FIELDS OF TABLE I_INVOICE

FROM VBRK AS A INNER JOIN

KNA1 AS B ON

A_{KUNAG = B}KUNNR

WHERE A~VBELN IN P_VBELN.

Reward points if helpful.

Regards,

Hemant.

Re: Join

Former Member — Thu, 06 Sep 2007 15:31:12 GMT

Hi,

First make not that you can not Join the table which is Cluster table. BSEG is one of that kind of table and you can not join these

I would suggest Select from BKPF and then go to BSEG and use For all entries

and then go to SKAT ans use For all Entries and same way for LFA1

Thanks,

Re: Join

Former Member — Thu, 06 Sep 2007 15:34:28 GMT

JOin on 4 different tables is not recommended and BSEG is a cluster.

Try creating Views and use it or just go for select queries individually.

Shreekant

Re: Join

Former Member — Fri, 07 Sep 2007 05:56:18 GMT

hi,

we can join those 4 tables , but in the performance level it is not recommended. But i am forwarding the sample code for that , go through it.

select a~bukrs

a~belnr

b~gjahr

b~buzei

c~lifnr

c~land1

d~saknr

d~mcod1

from bkpf as inner join bseg as b

on a_{bukrs = b}bukrs

inner join lfa1 as c

on b_{lifnr = c}lifnr

inner join skat as d

on c_{mcod1 = d}mcod1.

please reward points if helpfull.

with regards,

radhika kolluru.

Re: Join

Former Member — Fri, 07 Sep 2007 06:01:27 GMT

Hi,

BESG is clustertable and JOINS doesnt work on Cluster tables.

Regards,

Naveen