Skip to Content
0
May 19, 2016 at 07:41 PM

Can we pass the input parameter to group by expression and handle the dynamic grouping in SAP HANA?

425 Views

Can we pass the input parameter to group by expression and handle the dynamic grouping in SAP HANA?

We have a requirement of grouping the data using the values provided by the user dynamically. I tried to create a CV which accepts an input parameter such that it groups the data based on the value provided by the user in the parameter window.

For example, there is a table GRADE_DETAILS in STUDENT schema.

This table contains marks of all students over years for all subjects they have attended. The table contains the columns

-------------------------------------------------------

Student name, Year, Total Marks

--------------------------------------------------------

A1 2010 80

A1 2011 70

A1 2012 60

B1 2010 40

B1 2011 30

B1 2012 20

--------------------------------------------------------


Student name will be the input parameter for the CV and based on the user input the view should display the total marks of student A1 or B1.

INPUT PARAMETER in this view is STUD_NAME.

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

BEGIN

VAR_OUT = SELECT CASE WHEN :STUD_NAME='A1' THEN A1

WHEN :STUD_NAME='B1' THEN B1

ELSE 'None'

END AS STUDENT,

SUM(TOTAL_MARKS)

FROM STUDENT.GRADE_DETAILS

GROUP BY

CASE WHEN :STUD_NAME='A1' THEN A1

WHEN :STUD_NAME='B1' THEN B1

ELSE 'None'

END;

END;

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------


When above view is activated, it trigger an error.

SAP DBTech JDBC: [277]: not a GROUP BY expression: line 1.