Error while executing Procedure

[sreelatha_reddy2]

Hi All

I have executed the below procedure. I got the below error. My doubt is why should I used GroupBy clause from Product_Name. If I am not using Group by clause, error is thrown up.

Create Procedure "Sales-Product"."Sales"

(IN Discount Integer ,

  OUT OUTPUT_TABLE "Sales-Product"."TT-Sales") AS

  BEGIN

  VAR1 = SELECT T1.REGION_NAME,T1.SUB_REGION_NAME,T2.PRODUCT_ID,T2.SALES_AMOUNT

         From "Sales-Product"."REGION" AS T1

           INNER JOIN

              "Sales-Product"."SALES" As T2

         ON T1.REGION_ID = T2.REGION_ID;

  VAR2 = SELECT T1.REGION_NAME,T1.SUB_REGION_NAME,T1.PRODUCT_ID,T1.SALES_AMOUNT,T2.PRODUCT_NAME

         From :VAR1 AS T1

           INNER JOIN

          "Sales-Product"."PRODUCT" AS T2

          ON T1.PRODUCT_ID = T2.PRODUCT_ID;

  OUTPUT_TABLE = SELECT SUM(SALES_AMOUNT) AS SALES_AMOUNT ,

                        PRODUCT_NAME,

                        PRODUCT_ID,

                        REGION_NAME,

                        SUB_REGION_NAME

                 FROM :VAR2;

    END

Error :

SAP DBTech JDBC: [260] (at 675): invalid column name: The column 'VAR2.PRODUCT_NAME' is invalid in the select list because the GROUP BY clause or an aggregation function does not contain it: line 16 col 25 (at pos 675)

[vivekbhoj]

Hi Sreelatha,

The reason is because you are using Aggregation function "SUM" so you need to include GROUP BY clause.

As you have listed PRODUCT_NAME column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The PRODUCT_NAME, PRODUCT_ID, REGION_NAME, SUB_REGION_NAME fields must, therefore, be listed in the SQL GROUP BY section.

Regards,

Vivek