Getting and transforming data in code to data paradigm

Former Member — Thu, 02 Oct 2014 12:37:11 GMT

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;

Re: Getting and transforming data in code to data paradigm

sundaresan_k — Sat, 04 Oct 2014 02:16:21 GMT

Dear Jakob,

The "Code to Data" at this level as in your example can be achieved with advanced Open SQL. In this case I would suggest use of expressions in Open SQL statements using the CASE construct as follows


  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,
    case be~shkzg
        when 'H' then be~menge * -1
        else
          be~menge end 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
   into table @data(lt_result) .

Please refer to documentation on new open SQL syntax and features here.

Information about support for expressions in Open SQL

Hope this helps.

Best regards

Sundar

topic Re: Getting and transforming data in code to data paradigm in Application Development and Automation Discussions

Getting and transforming data in code to data paradigm

Re: Getting and transforming data in code to data paradigm