issue with AMDP usage SQL scrpt

[Former Member]

Hi Experts,

I am writing the below SQL script , I am having an issue with SQL SCRIPT Writing for all entries please suggest

CLASS zbalu_amdp3 DEFINITION

  PUBLIC

  FINAL

  CREATE PUBLIC .

  PUBLIC SECTION.

  INTERFACES if_amdp_marker_hdb.

  TYPES:BEGIN OF t_vbakap,

        vbeln type vbak-vbeln,

        vkorg type vbak-vkorg,

        vtweg type vbak-vtweg,

        spart type vbak-spart,

        kunnr type vbak-kunnr,

        posnr type vbap-posnr,

        matnr type vbap-matnr,

        matwa type vbap-matwa,

        END OF t_vbakap.

   types: begin of t_kna1,

          kunnr type kna1-kunnr,

          name1 TYPE kna1-name1,

          land1 type kna1-land1,

          land2 type kna1-land2,

          ort01 TYPE kna1-ort01,

          END OF t_kna1.

   TYPES: begin of t_final,

        vbeln type vbak-vbeln,

        vkorg type vbak-vkorg,

        vtweg type vbak-vtweg,

        spart type vbak-spart,       

        kunnr type vbak-kunnr,

        posnr type vbap-posnr,

        matnr type vbap-matnr,

        matwa type vbap-matwa,

        name1 TYPE kna1-name1,

        land1 type kna1-land1,

        land2 type kna1-land2,

        ort01 TYPE kna1-ort01,

          END OF t_final.

  data: w_kna1 TYPE t_kna1,

        i_kna1 TYPE STANDARD TABLE OF t_kna1.

  TYPES: tt_final TYPE STANDARD TABLE OF t_final,

         tt_kna1 TYPE STANDARD TABLE OF t_kna1,

         tt_vbakap TYPE STANDARD TABLE OF t_vbakap.

   METHODS: get_data IMPORTING

                     VALUE(iv_vbeln) type vbak-vbeln

                     exporting value(et_final) type tt_final

                     VALUE(et_vbakap) type tt_vbakap

                     VALUE(et_kna1) TYPE tt_kna1.

  PROTECTED SECTION.

  PRIVATE SECTION.

ENDCLASS.

CLASS zbalu_amdp3 IMPLEMENTATION.

METHOD get_data BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT using vbak vbap kna1.

  TYPES: BEGIN OF t_vbakap,

        vbeln type vbak-vbeln,

        vkorg type vbak-vkorg,

        vtweg type vbak-vtweg,

        spart type vbak-spart,

        kunnr type vbak-kunnr,

        posnr type vbap-posnr,

        matnr type vbap-matnr,

        matwa type vbap-matwa,

        END OF t_vbakap.

   types: begin of t_kna1,

          kunnr type kna1-kunnr,

          name1 TYPE kna1-name1,

          land1 type kna1-land1,

          land2 type kna1-land2,

          ort01 TYPE kna1-ort01,

          END OF t_kna1.

data: w_kna1 type t_kna1, w_vbakap type t_vbakap.

et_vbakap  =  SELECT a.vbeln , a.vkorg , a.vtweg , a.spart , a.kunnr

              b.posnr , b.matnr , b.matwa

              from vbak as a

              inner join vbap as b

              on a.vbeln = b.vbeln ;

LOOP at et_vbakap into w_vbakap.

exec sql.

  select kunnr name1 land1 land2 ort01 into :w_kna1 where kunnr eq w_vbakap-kunnr.

  if  sy-subrc is initial.

    append w_kna1 to et_kna1.

  endif.

endloop.            

I am having error at loop.. please suggest how to get enrtries from KNA1 table based on entries in VBAK / VBAP tables ie from internal table et_vbakap table.

Thanks,

RG

ENDMETHOD..

[pfefferf]

Hello Ramya,

in an AMDP you cannot use ABAP syntax like LOOP. For SQLScript there exists imperative loop constructs (check the SQLScript Dev Guide for that), but they are not necessary for your case.

You can get the data either by using a join

SELECT DISTINCT t1.KUNNR, t1.NAME1, t1.LAND1, t1.LAND2, t1.ORT1
FROM KNA1 AS t1
INNER JOIN :et_vbakap AS t2
ON t1.kunnr = t2.kunnr;

or using a sub select.

SELECT t1.KUNNR, t1.NAME1, t1.LAND1, t1.LAND2, t1.ORT1
FROM KNA1 AS t1
WHERE t1.KUNNR IN (SELECT DISTINCT KUNNR FROM :et_vbakap);

Regards,

Florian