topic Re: select join query in AMDP in Application Development and Automation Discussions

select join query in AMDP

Former Member — Tue, 23 Jun 2020 01:11:32 GMT

Hi All,

Can anyone help me to fix below query.

I am working on AMDP and I need to write a select query in AMDP using select join condition so I can get filtered values in internal table. Below is the code which I have written in old abap program. Please let me know how we can convert same code in AMDP.

select vbeln, posnr, parvw, kunnr from vbrp into table @data(lt_vbrp1) 
for all entries in lt_itab 
where vbeln = lt_itab-aubel and posnr = lt_itab-aupos and parvw = 'WE'.


*-- get values from Header (here header values are stored on 000000 line item)
select vbeln, posnr, parvw, kunnr from vbrp into table @data(lt_vbrp2) for all entries in lt_itab
where vbeln = lt_itab-aubel and posnr = '000000' and parvw = 'WE'.

sort lt_vbrp1 by vbeln posnr.
sort lt_vbrp2 by vbeln posnr.

loop at lt_itab into data(ls_itab).
read table lt_vbrp1 into data(ls_vbrp) with key vbeln = ls_itab-aubel posnr = ls_itab-aupos binary search.
if sy-subrc eq 0.
* append data to itab_final.
else.
read table lt_vbrp2 into ls_vbrp with key vbeln = ls_itab-aubel posnr = '000000' binary search.
* append data to itab_final.
endif.

clear ls_vbrp.
endloop.

Thanks in Advance.

-- Vijendra

Re: select join query in AMDP

former_member1716 — Tue, 23 Jun 2020 04:32:46 GMT

vijendra07,

To Answer your question you must have an understanding of AMDP methods first which will help in understanding the solution part.

Not all part of your code can be converted into AMDP, You have to understand and get to decide which part of the Logic should you push into the AMDP and which part you would like to handle at ABAP editor.

To be clear on the all the above concepts its imperative that you understand the concepts first and then you yourself would be able to reach the solution part.

To start with:

1) Understand the theoretical part of AMDP.

2) Start following simple examples.

3) Start coding few exercises.

There are lot of articles on the same in the FORUM, you can try searching.

All the Best!!

Cheers!

Re: select join query in AMDP

Former Member — Fri, 26 Jun 2020 06:03:49 GMT

Hi Satish Kumar,

Thanks for your response, but this is not the answer which I am expecting here.

Re: select join query in AMDP

MateuszAdamus — Fri, 26 Jun 2020 07:26:45 GMT

Hello vijendra07

First of all, VBRP table does not contain PARVW field. Did you mean VBPA?

Here's an idea, assuming you did mean VBPA and you're providing LT_ITAB as a parameter to the AMDP .

SELECT 
  CASE WHEN b~vbeln IS NOT NULL THEN b~vbeln ELSE c~vbeln END AS vbeln,
  CASE WHEN b~posnr IS NOT NULL THEN b~posnr ELSE c~posnr END AS posnr,
  CASE WHEN b~parvw IS NOT NULL THEN b~parvw ELSE c~parvw END AS parvw,
  CASE WHEN b~kunnr IS NOT NULL THEN b~kunnr ELSE c~kunnr END AS kunnr
  FROM :it_itab AS a
  LEFT OUTER JOIN vbpa AS b
    ON b~vbeln = a~aubel
    AND b~posnr = a~aupos
    AND parvw = 'WE'
  LEFT OUTER JOIN vbpa AS c
    ON b~vbeln = a~aubel
    AND b~posnr = 0
    AND parvw = 'WE'
  WHERE ( b~parvw IS NOT NULL OR ( b~parvw IS NULL and c~parvw IS NOT NULL ) ).

Kind regards,

Mateusz