Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to use the AS table??? Expand / Collapse
Author
Message
Posted Tuesday, May 21, 2013 1:53 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
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.

Post #1454847
Posted Tuesday, May 21, 2013 2:03 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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

Post #1454854
Posted Tuesday, May 21, 2013 2:11 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
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
Post #1454861
Posted Tuesday, May 21, 2013 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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
Post #1454866
Posted Tuesday, May 21, 2013 2:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
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.

Post #1454867
Posted Tuesday, May 21, 2013 3:27 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 10, 2014 7:07 AM
Points: 3,545, Visits: 2,654
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.
Post #1454892
Posted Tuesday, May 21, 2013 3:55 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 3:13 AM
Points: 81, Visits: 174
TNX A LOT it works :)

Up to my next Qeustion :)
Post #1454899
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse