Problem in Running This Statement in SQL 2000

[sherly]

UPDATE SecCounter
SET HHigh = Max ( SELECT TOP 200 C.High
FROM CONS C
GROUP BY C.CounterName
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC
)
, CloseV = Max ( SELECT TOP 1 C.CloseV
FROM CONS C
GROUP BY C.CounterName
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC
)
, AvgVol = Max ( SELECT TOP 200 C.Vol
FROM CONS C
GROUP BY C.CounterName
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC
)
FROM SecCounter S
WHERE S.Sector = 'CONS'


---------------------------------
Why the statement above wouldnt work in my SQL 2000. It keeps giving me the error message of
"Incorrect syntax near the keyword of 'SELECT' "

Please help!
Thanks!
Sherly

 

[Raj1Patel]

You have placed the Group By in the wrong place on all the Statements.

Try the following

UPDATE SecCounter
SET HHigh = Max (SELECT TOP 200 C.High
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
, CloseV = Max ( SELECT TOP 1 C.CloseV
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
, AvgVol = Max ( SELECT TOP 200 C.Vol
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
FROM SecCounter S
WHERE S.Sector = 'CONS'

Raj

 

[williamu]

Here, try this.

UPDATE SecCounter
SET HHigh = (SELECT TOP 200 Max(C.High)
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
, CloseV = (SELECT TOP 1 Max(C.CloseV)
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
, AvgVol = (SELECT TOP 200 Max(C.Vol)
FROM CONS C
WHERE C.CounterName = S.CounterName
GROUP BY C.CounterName
ORDER BY CounterDate DESC
)
FROM SecCounter S
WHERE S.Sector = 'CONS'
William
Software Engineer
ICQ No. 56047340

 

[sherly]

There's an error message of :
Column 'C.High' is invalid in the select list because it is not contained in either an aggregate function
or the GROUP BY Clause

 

[williamu]

The above is in reference to which version of the statement, Raj's, Mine or your own?



William
Software Engineer
ICQ No. 56047340

 

[sherly]

Hi Williamu, I tried your statement and it gives me the error message of
Column 'C.CounterDate' is invalid in the select list because it is not contained in either an aggregate function
or the GROUP BY Clause

 

[sherly]

This one is for the version of Raj1Patel.

There's an error message of :
Column 'C.High' is invalid in the select list because it is not contained in either an aggregate function
or the GROUP BY Clause

 

[williamu]

Have you tried my version of the Query?

William
Software Engineer
ICQ No. 56047340

 

[williamu]

Have you tried ORDER BY C.CounterDate ?

William
Software Engineer
ICQ No. 56047340

 

[sherly]

Yes, I tried yours, William. I got this error message :

Column 'C.CounterDate' is invalid in the select list because it is not contained in either an aggregate function
or the GROUP BY Clause

 

[sherly]

Yes, I tried putting C.CounterDate, still the same err msg
Column 'C.CounterDate' is invalid in the select list because it is not contained in either an aggregate function
or the GROUP BY Clause

 

[rksingh024]

Hi Sherly,

What exactly you want to do. If any case you resolve the problem of "Column 'C.High' is invalid in the select list because it is not contained in either an aggregate function " then you will get different error message stating that subquery returned more than one value.

I think you want to do this:

UPDATE SecCounter
SET HHigh = ( SELECT Max (C.High)
FROM CONS C
WHERE C.CounterName = S.CounterName
)
, CloseV = ( SELECT Max (C.CloseV)
FROM CONS C
WHERE C.CounterName = S.CounterName
)
, AvgVol = ( SELECT Max (C.Vol)
FROM CONS C
WHERE C.CounterName = S.CounterName
)
FROM SecCounter S
WHERE S.Sector = 'CONS'


Have a nice time
Ramesh

 

[tlbroadbent]

Try the following.

UPDATE SecCounter
SET HHigh = (Select Max(High) From
(SELECT TOP 200 C.High FROM CONS C
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC) q1)
, CloseV =
(SELECT TOP 1 C.CloseV FROM CONS C
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC)
, AvgVol = (Select Max(vol) From
(SELECT TOP 200 C.Vol FROM CONS C
WHERE C.CounterName = S.CounterName
ORDER BY CounterDate DESC) q2)
FROM SecCounter S
WHERE S.Sector = 'CONS'
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.