Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Using case in select query join

aks778
Explorer
0 Kudos

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 ?

9 REPLIES 9

AlexGourdet
Community Manager
Community Manager
0 Kudos

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

Patrice
Participant
0 Kudos

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

0 Kudos

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

0 Kudos

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
Contributor
0 Kudos

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
Explorer
0 Kudos

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"

)

0 Kudos

Can you please write it syntatically ?

Sandra_Rossi
Active Contributor
0 Kudos

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

aks778
Explorer
0 Kudos

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