Problem with SQL Group by and having

[Former Member]

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.

[RaymondGiuseppi]

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

[Former Member]

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" ( ? )

[retired_member]

No, that's not the case.

[retired_member]

"If no GROUP BY grouping is applied to columns specified after HAVING outside of aggregate functions...

[Former Member]

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.

[RaymondGiuseppi]

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