Skip to Content
avatar image
Former Member

Problem with SQL Group by and having

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.
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Feb 10, 2017 at 10:37 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Feb 10, 2017 at 10:44 AM

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

    Add comment
    10|10000 characters needed characters exceeded

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

    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.
    
    Add comment
    10|10000 characters needed 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