Skip to Content
0

Problem with SQL Group by and having

Feb 10, 2017 at 10:21 AM

70

avatar image
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.
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Raymond Giuseppi
Feb 10, 2017 at 10:37 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Feb 10, 2017 at 10:44 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

No, that's not the case.

0
avatar image
Former Member Feb 10, 2017 at 10:53 AM
0

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.
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0
Horst Keller
Feb 10, 2017 at 10:48 AM
0
Share
10 |10000 characters needed characters left characters exceeded