issue with AMDP usage SQL scrpt

Former Member — Tue, 27 Sep 2016 02:00:22 GMT

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,

ENDMETHOD..

Re: issue with AMDP usage SQL scrpt

pfefferf — Tue, 27 Sep 2016 05:35:55 GMT

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

topic issue with AMDP usage SQL scrpt in Application Development and Automation Discussions

issue with AMDP usage SQL scrpt

Re: issue with AMDP usage SQL scrpt