SAP HANA SQl Query

[Former Member]

Hi

     I have this query

Select
A.CalDay,
Sum(Case When Left(A.Suffix,1) = 'B' Then
  (Case when A.SK_400ID is null Then
    Max(A.QualityScore)
  Else
   (Select Min(C.QualityScore)
  From DATAINT.TBL_NEM400 B, DATAINT.TBL_QUALITYSCORE_LU C
  Where A.SK_400ID = B.SK_400ID
  And Left(B.QualityMethod,1) = C.QualityMethod
  Group By B.SK_400ID)
End)
Else
0
End) As NewQualityScore_B,
Sum(Case When Left(A.Suffix,1) = 'E' Then
  (Case when A.SK_400ID is null Then
    Max(A.QualityScore)
  Else
   (Select Min(C.QualityScore)
  From DATAINT.TBL_NEM400 B, DATAINT.TBL_QUALITYSCORE_LU C
  Where A.SK_400ID = B.SK_400ID
  And Left(B.QualityMethod,1) = C.QualityMethod
  Group By B.SK_400ID)
End)
Else
0
End) As NewQualityScore_E


From DATAINT.TBL_SMAPROF A

Where NMI = '2001000639'
And CalDay >= '2013-07-01'
And CalDay < '2013-09-29'

Group By A.CalDay,A.SK_400ID

Order by A.CalDay

  Which gives me this error

  Could not execute 'Select A.CalDay, Sum(Case When Left(A.Suffix,1) = 'B' Then (Case when A.SK_400ID is null Then ...' in 6 ms 444 µs . SAP DBTech JDBC: [279] (at 102): group function is nested: line 5 col 5 (at pos 102)

Any sugestions on how I can do this. I have been doing this by puting the Suffix part in the where clause but that is to slow

Thanks MarkS

[brenton_ocallaghan]

Have you tried changing your group by clause to:#

Group By A.CalDay, NewQualityScore_B, NewQualityScore_E
Order by A.CalDay

OR

Group By A.CalDay
Order by A.CalDay

I can't try this out at the moment unfortunately as I am not near my system but you are grouping by a field that is not available at that point which is causing your error.

Thanks,

Brenton.

[Former Member]

The first sugestion just give a 'invalid column name'. The second suggestion give the original error messge

[rindia]

Hi Marks,

In your SELECT clause there is 1 simple column and the other is aggregated. In Group By clause, if you remove A.SK_400ID then it will work I guess.

Result of your query will be Calday and Sum(QualityScore).

Regards

Raj

[Former Member]

Tried that, I still get the same error message