08-21-2013 7:02 AM
Hi Experts,
I have a select option field SO_FKDAT (DATE FIELD) in a selection screen. Based on the value of SO_FKDAT field I have to take the records from the VBRK table. Then with the VBELN field of VBRK I have to take value from VBRP table.
Here is the code I wrote for achieving the same and it is working perfectly.
SELECT VBRK~FKDAT VBRK~VBELN VBRK~FKART VBRK~KUNAG VBRK~KUNRG VBRK~VKORG VBRK~VTWEG VBRK~NETWR VBRP~VKBUR
INTO CORRESPONDING FIELDS OF TABLE IT_FINOVR FROM VBRK JOIN VBRP ON ( VBRK~VBELN = VBRP~VBELN ) WHERE VBRK~FKDAT IN SO_FKDAT.
But I want to know whether there are any other coding styles to achieve the same. For All Entries In is not an option since it does not take duplicate entries.
Regards,
Marina.
08-21-2013 7:09 AM
Hi Marina,
in your case inner join is a good solution
SELECT VBELN FKART KUNAG KUNRG ...
INTO IT_VBRK
FROM VBRK
WHERE KFDAT IN S_FKDAT.
CHECK NOT IT_VBRK[] IS INITIAL.
SELECT VBELN VKBUR ...
INTO IT_VBRP
FROM VBRP
FOR ALL ENTRIES IN IT_VBRK
WHERE VBELN EQ IT_VBRK-VBELN.
LOOP AT IT_VBRK.
READ IT_VBRP ...
ENDLOOP.
(you could improve performance of the LOOP)
regards
Fred
08-21-2013 7:13 AM
Hi Fred,
Thanks for your solution. But I have tried with FOR ALL ENTRIES IN. But FAE does not give you duplicate values. I need to have duplicate values as well.
Regards,
Marina.
08-21-2013 7:15 AM
08-21-2013 7:26 AM
Hi Marina,
When you post an FAE SAP does an internal delete adjacent duplicates to remove if any duplicates so you nedd to be sure to add the key fields of the resultant table in it.
So as recommended by Fred you can add posnr which will make it unique.
But as for for best solution your query already is a best performing as FAE with huge data will be slower than join and you will be reading the header increasing the processing time too.
JOIN is the best option in 1 : N scenario when you have key same,
Regards
08-21-2013 7:13 AM
Hi Marina,
The use of 'join' is a good option and i think that you can proceed with it(if "for all entries" is not useful for you).
Regards,
Shahir Mirza
08-21-2013 7:36 AM
Another thought might be to try LDB VFV? Sometimes I have managed to reduce processing time by using logical databases.
Just an idea, hope it helps.
08-21-2013 7:44 AM
Hi Marina,
Even if you use for all entries it will fetch all records provided you need to select all key fields.
Try this this will resolve your Issue.But still i think for VBRK & VBRP select using join is better option.
08-21-2013 7:49 AM
Hi Marina Debrova,
VBRK - Billing Document: Header Data
VBRP - Billing Document: Item Data
Key filed between VBRK & VBRP
VBELN - Billing Document
POSNR is item filed for VBELN - Billing Document
So try like this you will get as per your requirement
TABLES : vbrk.
TYPES : BEGIN OF ty_finovr,
fkdat TYPE vbrk-fkdat,
vbeln TYPE vbrk-vbeln,
fkart TYPE vbrk-fkart,
kunag TYPE vbrk-kunag,
kunrg TYPE vbrk-kunrg,
vkorg TYPE vbrk-vkorg,
vtweg TYPE vbrk-vtweg,
netwr TYPE vbrk-netwr,
vkbur TYPE vbrp-vkbur,
posnr TYPE vbrp-posnr,
END OF ty_finovr.
DATA : it_finovr TYPE TABLE OF ty_finovr.
SELECT-OPTIONS : so_fkdat FOR vbrk-fkdat.
SELECT vbrk~fkdat
vbrk~vbeln
vbrk~fkart
vbrk~kunag
vbrk~kunrg
vbrk~vkorg
vbrk~vtweg
vbrk~netwr
vbrp~vkbur
vbrp~posnr
INTO CORRESPONDING FIELDS OF TABLE it_finovr FROM vbrk JOIN vbrp ON ( vbrk~vbeln = vbrp~vbeln )
WHERE vbrk~fkdat IN so_fkdat.
BREAK-POINT.
inner join is better ....So please follow as Fedric suggestion.....
08-21-2013 8:29 AM
You said it works perfectly. Why look for second best alternatives?
Thomas
08-21-2013 8:44 AM