Using case in select query join

[aks778]

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 ?

[AlexGourdet]

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:

• Profile https://developers.sap.com/tutorials/community-profile.html
• Tips for Questions: https://community.sap.com/resources/questions-and-answers
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,

Alex

[Patrice]

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

[aks778]

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

[Patrice]

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

[roberto_forti]

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.

[shantraj]

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"

)

[aks778]

Can you please write it syntatically ?

[Sandra_Rossi]

Could you clarify where ADRNR is taken from? Any SELECT to share that we could fix?

[aks778]

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