Question Re: is possible to use aggregate function in looping internal table? in Technology Q&A

is possible to use aggregate function in looping internal table?

former_member635273 — Thu, 02 Apr 2020 04:58:57 GMT

actually i want to get max date (j~fkdat) and max time.(j~ezret)

this my code.

SELECT  a~vbeln, a~posnr, a~posex, a~matnr, a~arktx, a~kwmeng, a~vrkme,
        b~audat, b~auart, b~bstnk, b~waerk, b~vbtyp, b~vkorg,
        d~name1, d~stras, d~ort01, d~land1,
        e~vbeln AS vbeln_od, e~posnr AS posnr_od, e~lfimg, e~vrkme AS vrkme_od,
        f~bldat, f~lfart, f~wadat_ist, f~wadat,
        g~wbstk,
        h~fksta,
        i~vbeln AS vbeln_bil, i~posnr AS posnr_bil, i~fkimg AS fkimg_bil, i~vrkme AS vrkme_bil, i~netwr AS netwr_bil,
        j~fkart,j~fkdat ,j~erzet
        FROM vbap AS a
        JOIN vbak AS b ON a~vbeln = b~vbeln
        JOIN vbpa AS c ON b~vbeln = c~vbeln
        JOIN kna1 AS d ON b~kunnr = d~kunnr
        LEFT JOIN  lips AS e ON a~vbeln = e~vgbel AND a~posnr = e~vgpos
        JOIN likp AS f ON e~vbeln = f~vbeln
        JOIN vbuk AS g ON f~vbeln = g~vbeln
        JOIN vbup AS h ON e~vbeln = h~vbeln AND e~posnr = h~posnr
        LEFT JOIN  vbrp AS i ON e~vbeln = i~vgbel AND e~posnr = i~vgpos
        JOIN vbrk AS j ON i~vbeln = j~vbeln
        INTO CORRESPONDING FIELDS OF TABLE @it_main
        WHERE a~pstyv NE 'ZRET' AND a~pstyv NE 'ZSER' AND a~pstyv NE 'ZSEV' AND a~pstyv NE 'TAD' AND a~pstyv NE 'ZTAD'
             AND b~vkorg IN @p_vkorg
             AND c~kunnr IN @p_kunnr1
             AND c~parvw EQ 'VE'
             AND d~kunnr IN @p_kunnr2.


  LOOP AT it_main INTO wa_main GROUP BY wa_main-vbeln.

Re: is possible to use aggregate function in looping internal table?

venkateswaran_k — Thu, 02 Apr 2020 05:05:27 GMT

Dear Saddam

You have big columns in your it_main table,

You are trying to group by vbeln only

What is your requirement?

Re: is possible to use aggregate function in looping internal table?

former_member635273 — Thu, 02 Apr 2020 06:12:03 GMT

I want to display data that has the latest date and time.i hv tried query like this.

SELECT  a~vbeln, a~posnr, a~posex, a~matnr, a~arktx, a~kwmeng, a~vrkme,
        b~audat, b~auart, b~bstnk, b~waerk, b~vbtyp, b~vkorg,
        d~name1, d~stras, d~ort01, d~land1,
        e~vbeln AS vbeln_od, e~posnr AS posnr_od, e~lfimg, e~vrkme AS vrkme_od,
        f~bldat, f~lfart, f~wadat_ist, f~wadat,
        g~wbstk,
        h~fksta,
        i~vbeln AS vbeln_bil, i~posnr AS posnr_bil, i~fkimg AS fkimg_bil, i~vrkme AS vrkme_bil, i~netwr AS netwr_bil,
        j~fkart,max( j~fkdat ) ,max( j~erzet )
        FROM vbap AS a
        JOIN vbak AS b ON a~vbeln = b~vbeln
        JOIN vbpa AS c ON b~vbeln = c~vbeln
        JOIN kna1 AS d ON b~kunnr = d~kunnr
        LEFT JOIN  lips AS e ON a~vbeln = e~vgbel AND a~posnr = e~vgpos
        JOIN likp AS f ON e~vbeln = f~vbeln
        JOIN vbuk AS g ON f~vbeln = g~vbeln
        JOIN vbup AS h ON e~vbeln = h~vbeln AND e~posnr = h~posnr
        LEFT JOIN  vbrp AS i ON e~vbeln = i~vgbel AND e~posnr = i~vgpos
        JOIN vbrk AS j ON i~vbeln = j~vbeln
        INTO CORRESPONDING FIELDS OF TABLE @it_main
        WHERE a~pstyv NE 'ZRET' AND a~pstyv NE 'ZSER' AND a~pstyv NE 'ZSEV' AND a~pstyv NE 'TAD' AND a~pstyv NE 'ZTAD'
             AND b~vkorg IN @p_vkorg
             AND c~kunnr IN @p_kunnr1
             AND c~parvw EQ 'VE'
             AND d~kunnr IN @p_kunnr2
      GROUP BY  a~vbeln, a~posnr, a~posex, a~matnr, a~arktx, a~kwmeng, a~vrkme,
        b~audat, b~auart, b~bstnk, b~waerk, b~vbtyp, b~vkorg,
        d~name1, d~stras, d~ort01, d~land1,
        e~vbeln , e~posnr, e~lfimg, e~vrkme ,
        f~bldat, f~lfart, f~wadat_ist, f~wadat,
        g~wbstk,
        h~fksta,
        i~vbeln , i~posnr , i~fkimg , i~vrkme , i~netwr ,
        j~fkart.

but i get error like this.

Re: is possible to use aggregate function in looping internal table?

venkateswaran_k — Thu, 02 Apr 2020 06:59:40 GMT

Hi Saddam

In your select statement j~fkart,max( j~fkdat ) ,max( j~erzet

you are using into corresponding fields of table @it_main

Please verify -

1. what is the corresponding field/colum you defined in that it_main table to hold this ?

2. Use that names here in the select statement as "xxxx"

Example: if you use d1, d2 for that fields, then use

j~fkart,max( j~fkdat ) as d1 ,max( j~erzet ) as d2

Update me if it works,

else we can go with other option

Re: is possible to use aggregate function in looping internal table?

Sandra_Rossi — Thu, 02 Apr 2020 07:53:30 GMT

The message says "Aggregate functions, host variables, or other expressions in the select list without alias names do not have a corresponding field."

Isn't it clear? Give a name to the aggregate functions, so that INTO CORRESPONDING finds a corresponding field for MAX, for instance if the target fields are named max_fkdat and max_erzet:

max( j~fkdat ) AS max_fkdat, max( j~erzet ) AS max_erzet

Re: is possible to use aggregate function in looping internal table?

former_member635273 — Thu, 02 Apr 2020 07:59:26 GMT

ok thnks sir. it's work