topic Re: Problem with SQL Group by and having in Application Development and Automation Discussions

Problem with SQL Group by and having

Former Member — Fri, 10 Feb 2017 10:21:35 GMT

I Have a Problem with the Following SQL Command:

The following SQL-Command throws a Runtime-Error

"The expression that contains MSPR~PSPNR is not a GROUP-BY expression."!

The Problem is, wehn I put PSPNR into the Group-by Expression, I dont get the sum I needed. I want the sum of prlab obber all PSRNR group by watnr, werks, lgort, charg.

What am I doing wrong? Or is this kind of statement not possible in ABAP?


    SELECT DISTINCT mspr~matnr AS matnr , mspr~werks AS werks,
                    mspr~lgort AS lgort,  mspr~charg AS charg,
             SUM( mspr~prlab ) AS prlab",
      INTO TABLE @et_avail_proj_stock
        FROM mspr
      GROUP BY matnr, werks, lgort, charg

      HAVING mspr~pspnr IN ( SELECT  pspnr 
                              FROM  prps
                              WHERE psphi  = ( SELECT psphi
                                                FROM  prps
                                                WHERE pspnr = @iv_wbs_element ) )
      AND  mspr~sobkz  = @zif_pp_po=>mc_special_stock_project
      AND  prlab       <> @lv_initial_prlab.

Re: Problem with SQL Group by and having

RaymondGiuseppi — Fri, 10 Feb 2017 10:37:33 GMT

The HAVING clause apply to the aggregated or group fields, so move other fields in a WHERE clause. Only aggregated field PRLAB should be in the HAVING clause?

Regards,
Raymond

Re: Problem with SQL Group by and having

Former Member — Fri, 10 Feb 2017 10:44:32 GMT

I am afraid where and Having in the same statement is not possible ( ? )

When theres a Group by there can only follow a "having" and never a "where" ( ? )

Re: Problem with SQL Group by and having

retired_member — Fri, 10 Feb 2017 10:48:09 GMT

"If no GROUP BY grouping is applied to columns specified after HAVING outside of aggregate functions, a syntax error occurs in the strict modes of the syntax check from Release 7.40, SP08. Outside of these strict modes, a syntax check warning is produced and a non-catchable exception is raised. The same applies to columns specified directly in the SELECT list when a HAVING clause is specified, but that are not specified after GROUP BY. "

Re: Problem with SQL Group by and having

retired_member — Fri, 10 Feb 2017 10:49:17 GMT

No, that's not the case.

Re: Problem with SQL Group by and having

Former Member — Fri, 10 Feb 2017 10:53:00 GMT

I have got it !

You have helped me indeed.

This is the correct statement:

 SELECT mspr~matnr AS matnr, mspr~werks AS werks,
           mspr~lgort AS lgort, mspr~charg AS charg,
           SUM( mspr~prlab ) AS prlab
      INTO TABLE @et_avail_proj_stock
        FROM mspr
      WHERE mspr~pspnr IN ( SELECT  pspnr " ALL WBS Elements of the Project
                              FROM  prps
                              WHERE psphi  = ( SELECT psphi         
                                                FROM  prps
                                                WHERE pspnr = @iv_wbs_element ) )
      AND  mspr~sobkz  = @zif_pp_po=>mc_special_stock_project
      AND  prlab       <> @lv_initial_prlab
      GROUP BY matnr, werks, lgort, charg.

Re: Problem with SQL Group by and having

RaymondGiuseppi — Mon, 13 Feb 2017 14:52:49 GMT

This give the correct result, actually the checked prlab is mspr~prlab for each record before aggregation, and correct only because mspr~prlab (some stock qty) is always positive, IMHO you could have written:

SELECT mspr~matnr AS matnr, mspr~werks AS werks,
           mspr~lgort AS lgort, mspr~charg AS charg,
           SUM( mspr~prlab ) AS prlab
      INTO TABLE @et_avail_proj_stock
        FROM mspr
      WHERE mspr~pspnr IN ( SELECT  pspnr " ALL WBS Elements of the Project
                              FROM  prps
                              WHERE psphi  = ( SELECT psphi         
                                                FROM  prps
                                                WHERE pspnr = @iv_wbs_element ) )
      AND  mspr~sobkz  = @zif_pp_po=>mc_special_stock_project
      GROUP BY matnr, werks, lgort, charg
      HAVING  prlab <> @lv_initial_prlab.

Regards,
Raymond