Skip to Content
0
Former Member
Oct 02, 2014 at 12:37 PM

Getting and transforming data in code to data paradigm

45 Views

Hi all,

I'm currently trying to develop a powerlist by joining three tables together, QCPR, EKBE and EKPO. However i need to transform a colum based on another column.

So when EKBE.SHKGZ = H then the value EKBE.MENGE needs to be negative.

I have got this to work using an analytic view and a formula. However i would like to do it in the code to data paradigm either by using a CDS view or an AMDP procedure.

I have tried both, but haven't gotten it to work yet. In the CDS view i can transform my data fine, however i am currently using the union all statement and therefore not getting the aggregated resultset which i want.

Then i tried to use a AMDP procedure, but here i can't activate it because my return type doesn't match, and i haven't been able to get any further.

I am hoping someone can help me out on how to do this. Thanks in advance.

Here is my method in the AMDP example

METHOD get_data BY DATABASE PROCEDURE FOR HDB

LANGUAGE SQLSCRIPT using QCPR EKBE EKPO.

output = select qc.CERTNO, qc.ZGTYP, qc.EBELN, qc.EBELP, qc.MATNR, qc.WERKS, qc.CHARG, qc.LICHN, qc.LFSNR,

qc.LIFNR, qc.HERSTELLER, qc.MJAHR, qc.MBLNR, qc.ZEILE, qc.BUDAT, qc.STATUS, qc.REMDAT, qc.ESCDAT,

qc.CERTDAT, qc.CERTREC, qc.ERSTELLER, qc.ERSTELDAT, qc.ERSTELZEIT, qc.AENDERER, qc.AENDERDAT, qc.AENDERZEIT, be.menge * -1 as menge, po.meins from QCPR as qc

inner join ekbe as be on qc.ebeln = be.ebeln

and qc.ebelp = be.ebelp

and qc.mjahr = be.lfgja

and qc.mblnr = be.lfbnr

and qc.zeile = be.lfpos

inner join ekpo as po on qc.ebeln = po.ebeln and qc.ebelp = po.ebelp

where be.shkzg = 'H';

output1 = select qc.CERTNO, qc.ZGTYP, qc.EBELN, qc.EBELP, qc.MATNR, qc.WERKS, qc.CHARG, qc.LICHN, qc.LFSNR,

qc.LIFNR, qc.HERSTELLER, qc.MJAHR, qc.MBLNR, qc.ZEILE, qc.BUDAT, qc.STATUS, qc.REMDAT, qc.ESCDAT,

qc.CERTDAT, qc.CERTREC, qc.ERSTELLER, qc.ERSTELDAT, qc.ERSTELZEIT, qc.AENDERER, qc.AENDERDAT, qc.AENDERZEIT, be.menge, po.meins from QCPR as qc

inner join ekbe as be on qc.ebeln = be.ebeln

and qc.ebelp = be.ebelp

and qc.mjahr = be.lfgja

and qc.mblnr = be.lfbnr

and qc.zeile = be.lfpos

inner join ekpo as po on qc.ebeln = po.ebeln and qc.ebelp = po.ebelp

where be.shkzg != 'H';

put3 = ce_join(:output, :output1, [CERTNO], [CERTNO, ZGTYP, EBELN, EBELP, MATNR, WERKS, CHARG, LICHN, LFSNR,

LIFNR, HERSTELLER, MJAHR, MBLNR, ZEILE, BUDAT, STATUS, REMDAT, ESCDAT,

CERTDAT, CERTREC, ERSTELLER, ERSTELDAT, ERSTELZEIT, AENDERER, AENDERDAT, AENDERZEIT, menge]);

et_output = select * from :output3;

And this is my CDS view

@AbapCatalog.sqlViewName: 'ZFSC_CERT'

define view ZFSC_CERTIFICATION_view as

select from QCPR as qc

inner join ekbe as be on qc.ebeln = be.ebeln

and qc.ebelp = be.ebelp

and qc.mjahr = be.lfgja

and qc.mblnr = be.lfbnr

and qc.zeile = be.lfpos

inner join ekpo as po on qc.ebeln = po.ebeln and qc.ebelp = po.ebelp

{

key qc.CERTNO as CERTNO,

key qc.ZGTYP as ZGTYP,

key qc.EBELN as EBELN,

key qc.EBELP as EBELP,

qc.MATNR as MATNR,

qc.WERKS as WERKS,

qc.CHARG as CHARG,

qc.LICHN as LICHN,

qc.LFSNR as LFSNR,

qc.LIFNR as LIFNR,

qc.HERSTELLER as HERSTELLER,

qc.MJAHR as MJAHR,

qc.MBLNR as MBLNR,

qc.ZEILE as ZEILE,

qc.BUDAT as BUDAT,

qc.STATUS as STATUS,

qc.REMDAT as REMDAT,

qc.ESCDAT as ESCDAT,

qc.CERTDAT as CERTDAT,

qc.CERTREC as CERTREC,

qc.ERSTELLER as ERSTELLER,

qc.ERSTELDAT as ERSTELDAT,

qc.ERSTELZEIT as ERSTELZEIT,

qc.AENDERER as AENDERER,

qc.AENDERDAT as AENDERDAT,

qc.AENDERZEIT as AENDERZEIT,

be.menge * -1 as menge

}

where shkzg = 'H'

group by qc.CERTNO, qc.ZGTYP, qc.EBELN, qc.EBELP, qc.MATNR, qc.WERKS, qc.CHARG, qc.LICHN, qc.LFSNR,

qc.LIFNR, qc.HERSTELLER, qc.MJAHR, qc.MBLNR, qc.ZEILE, qc.BUDAT, qc.STATUS, qc.REMDAT, qc.ESCDAT,

qc.CERTDAT, qc.CERTREC, qc.ERSTELLER, qc.ERSTELDAT, qc.ERSTELZEIT, qc.AENDERER, qc.AENDERDAT, qc.AENDERZEIT, po.meins, be.menge

union all

select from QCPR as qc

inner join ekbe as be on qc.ebeln = be.ebeln

and qc.ebelp = be.ebelp

and qc.mjahr = be.lfgja

and qc.mblnr = be.lfbnr

and qc.zeile = be.lfpos

inner join ekpo as po on qc.ebeln = po.ebeln and qc.ebelp = po.ebelp

{

key qc.CERTNO as CERTNO,

key qc.ZGTYP as ZGTYP,

key qc.EBELN as EBELN,

key qc.EBELP as EBELP,

qc.MATNR as MATNR,

qc.WERKS as WERKS,

qc.CHARG as CHARG,

qc.LICHN as LICHN,

qc.LFSNR as LFSNR,

qc.LIFNR as LIFNR,

qc.HERSTELLER as HERSTELLER,

qc.MJAHR as MJAHR,

qc.MBLNR as MBLNR,

qc.ZEILE as ZEILE,

qc.BUDAT as BUDAT,

qc.STATUS as STATUS,

qc.REMDAT as REMDAT,

qc.ESCDAT as ESCDAT,

qc.CERTDAT as CERTDAT,

qc.CERTREC as CERTREC,

qc.ERSTELLER as ERSTELLER,

qc.ERSTELDAT as ERSTELDAT,

qc.ERSTELZEIT as ERSTELZEIT,

qc.AENDERER as AENDERER,

qc.AENDERDAT as AENDERDAT,

qc.AENDERZEIT as AENDERZEIT,

sum(be.menge) as menge

}

where shkzg != 'H'

group by qc.CERTNO, qc.ZGTYP, qc.EBELN, qc.EBELP, qc.MATNR, qc.WERKS, qc.CHARG, qc.LICHN, qc.LFSNR,

qc.LIFNR, qc.HERSTELLER, qc.MJAHR, qc.MBLNR, qc.ZEILE, qc.BUDAT, qc.STATUS, qc.REMDAT, qc.ESCDAT,

qc.CERTDAT, qc.CERTREC, qc.ERSTELLER, qc.ERSTELDAT, qc.ERSTELZEIT, qc.AENDERER, qc.AENDERDAT, qc.AENDERZEIT, po.meins;