How to use alias column ?? :S - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to use alias column ?? :S

Author

Topic

Karim174
Starting Member

5 Posts

Posted - 2013-05-21 : 04:05:13

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.

SQL GETTING A DREAM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 04:49:40

you need to make it into derived table and use it at next level

like


select *
from
(
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
)t
where Inuse >= 80

Also TOP 10 doesnt make much sense without an ORDER BY.as of now it just returns 10 random rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Karim174
Starting Member

5 Posts

Posted - 2013-05-21 : 05:09:10

quote:
Originally posted by visakh16

you need to make it into derived table and use it at next level

like

select *
from
(
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
)t
where Inuse >= 80

Also TOP 10 doesnt make much sense without an ORDER BY.as of now it just returns 10 random rows

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

select TOP 10 Device.nDeviceID,
Device.sDisplayName,
sgroupname,
sDescription,
dPollTime,
MIN(nUsed_Min) AS Minimaal_gebruik,
MAX(nused_Max) AS Maximaal_gebruik,
SUM(nSize) AS Totaal_Schijfruimte,

(CAST(SUM(nUsed_Min) AS FLOAT(2)) / CAST(SUM(nSize) AS FLOAT(2)) * CAST(AVG(100) AS NUMERIC(10, 2))) AS Ingebruik

from dbo.StatisticalDisk

left outer join dbo.StatisticalDiskIdentification on StatisticalDiskIdentification.nStatisticalDiskIdentificationID = StatisticalDisk.nStatisticalDiskIdentificationID

left outer join dbo.PivotStatisticalMonitorTypeToDevice on PivotStatisticalMonitorTypeToDevice.nPivotStatisticalMonitorTypeToDeviceID = StatisticalDiskIdentification.nPivotStatisticalMonitorTypeToDeviceID

left outer join Device on Device.nDeviceID = PivotStatisticalMonitorTypeToDevice.nDeviceID
INNER JOIN pivotdevicetogroup ON PivotDeviceToGroup.nDeviceID = Device.nDeviceID
INNER JOIN DeviceGroup ON PivotDeviceToGroup.nDeviceGroupID = DeviceGroup.nDeviceGroupID
INNER JOIN time on StatisticalDisk.dPollTime = time.PK_Date
where StatisticalDisk.dPollTime = dateadd(MM, 1, 'time')
AND DeviceGroup.sGroupName IN ('CUSTOMER')
group by Device.nDeviceID,
Device.sDisplayName,
devicegroup.sgroupname,
sDescription,
dPollTime,
nSize
order by dPollTime desc

THis the syntax full... But what your sending is not possible.

SQL GETTING A DREAM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-21 : 05:10:48

why? whats the issue? Its still possible if used as suggested. WHats the column you want to filter on?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources