944,161 Members | Top Members by Rank

Web Development > Databases > MS SQL > Problem with query.
Ad:
  • MS SQL Discussion Thread
  • Unsolved
  • Views: 2265
  • MS SQL RSS
Permalink
Oct 24th, 2006
0

Problem with query.

Expand Post »
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.

MS SQL Syntax (Toggle Plain Text)
  1. /*INSERT INTO WERKS (
  2. WERKS.Z8601, 
  3. WERKS.Z8602, 
  4. WERKS.Z8603, 
  5. WERKS.Z8604, 
  6. WERKS.Z8605, 
  7. WERKS.Z8606, 
  8. WERKS.Z8607, 
  9. WERKS.Z8608, 
  10. WERKS.Z8609, 
  11. WERKS.Z8610, 
  12. WERKS.Z8611, 
  13. WERKS.Z8621, 
  14. WERKS.Z8622, 
  15. WERKS.Z8623, 
  16. WERKS.Z8624, 
  17. WERKS.Z8625, 
  18. WERKS.Z8626, 
  19. WERKS.Z8627, 
  20. WERKS.Z8628, 
  21. WERKS.Z8629, 
  22. WERKS.Z8630, 
  23. WERKS.Z8631, 
  24. WERKS.Z8632) */
  25. SELECT 
  26. 0, 
  27. Stati.z4414, 
  28. Stati.z4402, 
  29. '', 
  30. '', 
  31. '', 
  32. Isnull((SELECT Srtas.z02 FROM Srtas WHERE Srtas.z00 = Stati.z4400 AND Srtas.z01 = Stati.z4414), ''), 
  33. Isnull((SELECT Klant.z0102 FROM Klant WHERE Klant.z0101 = Stati.z4402), ''), 
  34. '', 
  35. '', 
  36. '', 
  37. sum (Case when Stati.z4407 = 200609 then Stati.z4409 Else 0 End), 
  38. sum (Case when Stati.z4407 = 200609 then Stati.z4410 Else 0 End), 
  39. sum (Case when Stati.z4407 = 200509 then Stati.z4409 Else 0 End), 
  40. sum (Case when Stati.z4407 = 200509 then Stati.z4410 Else 0 End), 
  41. sum (Case when Stati.z4407 BETWEEN 200510 AND 200609 then Stati.z4409 Else 0 End), 
  42. sum (Case when Stati.z4407 BETWEEN 200510 AND 200609 then Stati.z4410 Else 0 End), 
  43. sum (Case when Stati.z4407 BETWEEN 200410 AND 200509 then Stati.z4409 Else 0 End), 
  44. sum (Case when Stati.z4407 BETWEEN 200410 AND 200509 then Stati.z4410 Else 0 End), 
  45. sum (Case when Stati.z4407 = 200609 then Stati.z4411 Else 0 End), 
  46. sum (Case when Stati.z4407 = 200509 then Stati.z4411 Else 0 End), 
  47. sum (Case when Stati.z4407 BETWEEN 200510 AND 200609 then Stati.z4411 Else 0 End), 
  48. sum (Case when Stati.z4407 BETWEEN 200410 AND 200509 then Stati.z4411 Else 0 End) 
  49. FROM STATI  
  50. WHERE 
  51. (Stati.z4402 BETWEEN '40000' AND 'ZONE6') AND 
  52. (Stati.z4414 BETWEEN '2005028' AND '2005028') AND 
  53. (Stati.z4417 = 'A') 
  54. GROUP BY Stati.z4414, Stati.z4402
I get the following error:

MS SQL Syntax (Toggle Plain Text)
  1. Msg 8120, Level 16, State 1, Line 25
  2. 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.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
jtemperv is offline Offline
1 posts
since Oct 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MS SQL Forum Timeline: Formatted Output in Stored Procedure
Next Thread in MS SQL Forum Timeline: SAP db - how to get the time update statistics was performed on a table?





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC