2022 Nov 28 4:55 PM
Hello gurus, I want a select query where if adrnr value is not in vbpa table then I should take it from kna1 table . Using case how to werite the select query ?
2022 Nov 28 4:55 PM
Thank you for visiting SAP Community to get answers to your questions.
As you're looking to get most out of your community membership, please consider include a profile picture to increase user engagement & additional resources to your reference that can really benefit you:
I hope you find this advice useful, and we're happy to have you as part of SAP Community!
All the best,
Alex
2022 Nov 28 7:33 PM
Hi,
You do not provide a lot of information. The answer will depend on a lot of factors: is it an online transaction that processes a single sales document, or do you have a large amount of data to process in a background process? What key are you using to read the data?
But for instance, if you only have a single sales document to process, a single item and a single partner, you can simply join VBPA with KNA1 and compare the two values afterwards. Something like :
SELECT SINGLE v~adrnr k~adrnr INTO (lv_v_adrnr, lv_k_adrnr)
FROM vbpa AS v
JOIN kna1 AS k ON k~kunnr = v~kunnr
WHERE vbeln = lv_your_vbeln
AND posnr = lv_your_posnr
AND parvw = lv_your_parvw.
IF lv_v_adrnr IS NOT INITIAL.
* Use ADRNR from VBPA
ELSE.
* Use ADRNR from KNA1
ENDIF.
You can also join with table ADRC if necessary.
Hope this helps.
Patrice
2022 Nov 29 2:14 PM
I will first join vbak and vbap and the I will join with vbpa if vbpa-adrnr value is not null else I will join with kna1 table and finally I will join with adrc table in order to get the results. I can join with either vbpa or kna1 based on vbpa-adrnr is initia or not. We can use case here but I dont know how to do it. And using only one select query
2022 Nov 30 3:46 PM
Here is an example of what it could look like. Of course if you insist on doing it in one select, you can always join ADRC twice (AS a1 + AS a2) with LEFT JOINs. But I wouldn't recommend it. It would be overly complicated to read and you could not do DATE_FROM LE SY-DATUM in the SELECT because it is a LEFT JOIN and no fields from the right-hand table of a LEFT JOIN may appear in the WHERE. This would result in reading more data than needed and having to filter it afterwards.
SELECT SINGLE v~adrnr k~adrnr INTO (lv_v_adrnr, lv_k_adrnr)
FROM vbpa AS v
JOIN kna1 AS k ON k~kunnr = v~kunnr
WHERE vbeln = lv_your_vbeln
AND posnr = lv_your_posnr
AND parvw = lv_your_parvw.
IF lv_v_adrnr IS NOT INITIAL.
lv_v_adrnr = lv_k_adrnr.
ENDIF.
IF lv_v_adrnr IS NOT INITIAL.
SELECT * UP TO 1 ROWS FROM adrc INTO ls_adrc WHERE addrnumber EQ lv_v_adrnr
AND date_from LE sy-datum.
ENDSELECT.
ENDIF.
I hope this helps.
Patrice
2022 Nov 28 9:40 PM
Hi,
If the scenario has more than one Sales Document and Partners to be retrieved within related Customers but pay attention VBPA-PERNR when columns VBPA-KUNNR and VBPA-ADRNR are blank.
select
a~vbeln "Sales Document
a~posnr "Item
a~parvw "Partner function
a~kunnr "Customer
b~adrnr "Address
from VBPA as a INNER JOIN KNA1 as b ON b~kunnr = a~kunnr
appending table it_VBPA_KNA1
for all entries in it_VBAP "Sales document (item)
where a~vbeln eq it_VBAP-vbeln
and a~posnr eq it_VBAP-posnr
and a~parvw in s_parvw[] "range of Partners
and (a~kunnr ne '' and a~adrnr eq ''). "pay attention!!!
endselect.
if sy-subrc eq 0.
...
endif.
2022 Nov 29 7:24 AM
Select vbeln
posnr
parvw
kunnr
FROM
( (CASE WHEN "VBPA-ADRNR" = :is not initial THEN Select vbeln
posnr
parvw
kunnr
from VBPA where .....
ELSE
Select vbeln
posnr
parvw
kunnr
from KNA1 where .....END) AS "ADNRN"
)
2022 Nov 29 3:16 PM
2022 Nov 29 7:52 PM
Could you clarify where ADRNR is taken from? Any SELECT to share that we could fix?
2022 Nov 30 4:39 PM
I will first join vbak and vbap and the I will join with vbpa if vbpa-adrnr value is not null else I will join with kna1 table and finally I will join with adrc table in order to get the results. I can join with either vbpa or kna1 based on vbpa-adrnr is initia or not. We can use case here but I dont know how to do it. And using only one select query