May 21, 2013 at 1:53 am
Hello SQL GURU's,
Im try the following SQL syntqx to use the AS inUse column..
Select TOP 10 Device.nDeviceID,
Device.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min),
MAX(nused_Max),
SUM(nSize),
(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Inuse
from statisticalDisk
where Inuse >= 80
I get the following error:
Msg 207, Level 16, State 1, Line 28
Invalid column name 'Inuse'.
Im try to use the colomn to get only device with 80 or higher.
May 21, 2013 at 2:03 am
Use it like this:
Select TOP 10 Device.nDeviceID,
Device.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min),
MAX(nused_Max),
SUM(nSize),
(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Inuse
from statisticalDisk
where (CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) >= 80
May 21, 2013 at 2:11 am
I get this error first:
Msg 102, Level 15, State 1, Line 28
Incorrect syntax near '>'.
With the syntax of yours.
The I fix it with this syntax:
(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100)) >= 80)
Then i get the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CAST'.
Then I fit it with this syntax:
(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2)) >= 80)
Then I get the following error:
Msg 147, Level 15, State 1, Line 28
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
:S eehm
May 21, 2013 at 2:21 am
Does it helps (there was one extra ")" missing) ?
where (CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
May 21, 2013 at 2:23 am
No it dont help. Its say the follwoing thing:
Msg 1035, Level 15, State 10, Line 28
Incorrect syntax near 'CAST', expected 'AS'.
He aspect a AS () something.
When i use that it
((CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
No it say the following thing:
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'group'.
When I use this syntax:
((CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80)
It says the following thing:
Msg 147, Level 15, State 1, Line 28
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
May 21, 2013 at 3:27 am
Karim, I overlooked at the following message:
Msg 147, Level 15, State 1, Line 28
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
This clearly shows the wrong implementation while using aggregate function. The code should be like this:
Select TOP 10 nDeviceId,
sDisplayName,
sgroupname,
sdescription,
dpolltime,
MIN(nused_min),
MAX(nused_max),
SUM(nsize),
(CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Inuse
from statisticalDisk
group by nDeviceId,
sDisplayName,
sgroupname,
sdescription, dpolltime
having (CAST(SUM(nused_min) AS FLOAT(2)) / CAST(SUM(nsize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) >= 80
Check this out and let me now if it works. Look out for using aggregate functions usage in BOL.
May 21, 2013 at 3:55 am
TNX A LOT it works 🙂
Up to my next Qeustion 🙂
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply