MS SQL

Problem with query.

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Oct 2006

Posts: 1

Reputation: jtemperv is an unknown quantity at this point

Solved Threads: 0

jtemperv

Offline

Newbie Poster

Problem with query.

Oct 24th, 2006

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.

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
/*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
/*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:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
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.
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!

Last edited by jtemperv; Oct 24th, 2006 at 6:22 am.