Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Getting and transforming data in code to data paradigm

Former Member
0 Kudos

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;

1 ACCEPTED SOLUTION

sundaresan_k
Explorer
0 Kudos

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

1 REPLY 1

sundaresan_k
Explorer
0 Kudos

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