Problem with query.

[jtemperv]

Hi all,

I have a problem with an SQL-query and I don't know what the best solution would be to solve the problem.

/*INSERT INTO WERKS (
WERKS.Z8601, 
WERKS.Z8602, 
WERKS.Z8603, 
WERKS.Z8604, 
WERKS.Z8605, 
WERKS.Z8606, 
WERKS.Z8607, 
WERKS.Z8608, 
WERKS.Z8609, 
WERKS.Z8610, 
WERKS.Z8611, 
WERKS.Z8621, 
WERKS.Z8622, 
WERKS.Z8623, 
WERKS.Z8624, 
WERKS.Z8625, 
WERKS.Z8626, 
WERKS.Z8627, 
WERKS.Z8628, 
WERKS.Z8629, 
WERKS.Z8630, 
WERKS.Z8631, 
WERKS.Z8632) */
SELECT 
0, 
Stati.z4414, 
Stati.z4402, 
'', 
'', 
'', 
Isnull((select Srtas.z02 from Srtas where Srtas.z00 = Stati.z4400 and Srtas.z01 = Stati.z4414), ''), 
Isnull((select Klant.z0102 From Klant where Klant.z0101 = Stati.z4402), ''), 
'', 
'', 
'', 
sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End), 
sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End), 
sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End), 
sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End), 
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4409 Else 0 End), 
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4410 Else 0 End), 
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4409 Else 0 End), 
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4410 Else 0 End), 
sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End), 
sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End), 
sum (Case when Stati.z4407 Between 200510 and 200609 then Stati.z4411 Else 0 End), 
sum (Case when Stati.z4407 Between 200410 and 200509 then Stati.z4411 Else 0 End) 
FROM STATI  
WHERE 
(Stati.z4402 Between '40000' AND 'ZONE6') AND 
(Stati.z4414 Between '2005028' AND '2005028') AND 
(Stati.z4417 = 'A') 
GROUP BY Stati.z4414, Stati.z4402

I get the following error:

Msg 8120, Level 16, State 1, Line 25
Column 'STATI.Z4400' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I know it has something todo with the select on the table SRTAS, but what's the best way to solve this problem without the chance of getting a wrong result.
What I want to do with that SELECT on SRTAS is to get the description field, wich is stored in the table SRTAS. Maybe this can be accomplished with a better solution?

This query is dynamicly generated from within my program from what the user selected.

Also, if there are better ways to write the query, I would be happy to get some hints and tips.

Thanks in advance!