topic Re: Inner Join in Application Development and Automation Discussions

Inner Join

Former Member — Tue, 23 Oct 2007 00:01:34 GMT

Hi gurus,

I have a condition where I need to use Inner join to join three of the tables, (ekko,ekpo and Eket)

I need to select PO number, item no, matnr, and Price based on the following condition:

Ekko-bsart-> 'NB',

Ekko-ekgrp-> 234

Ekpo-elikz-> ' '

ekpo-afnam-> 'z'

and

Eket-eindt-> '10/10/2007'

can you please help me out.....

Thanks

Rajeev

Message was edited by:

Rajeev Gupta

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:07:07 GMT

It is not a good idea to join these 3 tables specially when you do not have primary keys to pass in WHERE Condition.

I suggest you first get data from EKPO - you can use standard index "A" while extracting data. You need to pass AFNAM and BSTYP in WHERE Condition. I believe you are populating PO data so BSTYP should be equal to "F". Get data into internal table IT_EKPO. Filter this based upon condition ELIKZ = space.

Then use IT_EKPO to get data from EKKO, get data into IT_EKKO, filter this based upon

Ekko-bsart-> 'NB',

Ekko-ekgrp-> 234

Then get data from EKET for all entries in IT_EKPO.

At the end you can combine these 3 tables.

Using JOIN in such case will increase runtime and program will be slow.

Hope this helps.

ashish

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:07:51 GMT

Hi Rajeev,

Can you paste your code. Will modify it if something wrong.

Regards,

Atish

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:09:03 GMT

Hi ashish thanks for the reply....

even I did the same but my team lead wants me to use inner join, so I will appreciate if you can tell me how to use inner join in this case.

Thanks

Rajeev

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:10:21 GMT

Hey Atish,

actually I haven't used Inner join before, I am new to ABAP and I was just using the simple select statement, but I have been advised to use the Inner join. so I will appreciate if you can help me out.

Thanks

Rajeev

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:13:36 GMT

Hi Rajeev,

Have you looked the sample code in ABAP help for JOINS. just try first it yourself, otherwise how will you learn :). If you miss anything or anything goes wrong we will definately help you. INNER JOIN is the most common feature of any SQL and I think you should first try.

Regards,

Atish

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:13:58 GMT

Ok here is the SELECT

SELECT EKKO~<FIELDNAME>

EKPO~<FIELDNAME>

EKET~<FIELDNAME>

INTO TABLE IT_EKKO

FROM EKKO

INNER JOIN EKPO ON

EKPO_{EBELN = EKKO}EBELN AND

EKPO~ELIKZ = SPACE AND

EKPO~AFNAM = 'Z'

INNER JOIN EKET ON

EKET_{EBELN = EKPO}EBELN AND

EKET_{EBELP = EKPO}EBELP AND

EKET~EINDT = 10/10/2007'

WHERE Ekko~bsart- = 'NB' AND

Ekko~ekgrp = '234'.

This is not a good idea to use join in this case. In above SELECT add your fields after SELECT.

Hope this helps.

ashish

Re: Inner Join

Former Member — Tue, 23 Oct 2007 00:16:39 GMT

Thanks very much for the reply Aashish..will definately discuss this with my team lead.

anyways thanks again!!! reward points will definately be awarded.

Rajeev

Re: Inner Join

Former Member — Tue, 23 Oct 2007 01:14:49 GMT

Hi ashish I wrote the code told by you but I am getting the following error:

Field "EKPO-EBELN" unknown.

can you please tel me whats the prob.

thanks

rajeev

Re: Inner Join

Former Member — Tue, 23 Oct 2007 01:16:02 GMT

Use '~' instead of '-'.

Re: Inner Join

Former Member — Tue, 23 Oct 2007 01:30:47 GMT

Hi,Rajeev.

EKKO,EKPO and EKET are Purchase Orders Tables,I found the relationship between them.

EKPO(Purchasing Document Item) includes Purchasing Doc(EBELN),Item(EBELP).

EKKO(Purchasing Document Header) includes Purchasing Doc(EBELN).

EKET(Delivery Schedules) includes Purchasing Doc(EBELN),Item(EBELP),Delivery Schedule(ETENR).

You can inner join three tables based this relationship.And I think it's no need to join three tables,because EKPO_{EBELN is PO number, EKPO}EBELP is item no,EKPO_{ZZITEM1 is matnr,and EKPO}NETPR is Price.Am I right?If there is something wrong,please reply me.

Regards,

feng.

Re: Inner Join

Former Member — Tue, 23 Oct 2007 04:12:49 GMT

Hi Atish,

Thanks for the nice advise, even I think that was the wrong appraoch ...I should have tried it first on my own..

Thanks again

Rajeev