10-23-2007 1:01 AM
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
10-23-2007 1:07 AM
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
10-23-2007 1:07 AM
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
10-23-2007 1:09 AM
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
10-23-2007 1:13 AM
Ok here is the SELECT
SELECT EKKO~<FIELDNAME>
EKPO~<FIELDNAME>
EKET~<FIELDNAME>
INTO TABLE IT_EKKO
FROM EKKO
INNER JOIN EKPO ON
EKPOEBELN = EKKOEBELN AND
EKPO~ELIKZ = SPACE AND
EKPO~AFNAM = 'Z'
INNER JOIN EKET ON
EKETEBELN = EKPOEBELN AND
EKETEBELP = EKPOEBELP 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
10-23-2007 1:16 AM
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
10-23-2007 2:14 AM
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
10-23-2007 2:16 AM
10-23-2007 1:07 AM
Hi Rajeev,
Can you paste your code. Will modify it if something wrong.
Regards,
Atish
10-23-2007 1:10 AM
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
10-23-2007 1:13 AM
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
10-23-2007 5:12 AM
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
10-23-2007 2:30 AM
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 EKPOEBELN is PO number, EKPOEBELP is item no,EKPOZZITEM1 is matnr,and EKPONETPR is Price.Am I right?If there is something wrong,please reply me.
Regards,
feng.