SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to use alias column ?? :S
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Karim174
Starting Member

5 Posts

Posted - 05/21/2013 :  04:05:13  Show Profile  Reply with Quote

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

India
52249 Posts

Posted - 05/21/2013 :  04:49:40  Show Profile  Reply with Quote
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
Go to Top of Page

Karim174
Starting Member

5 Posts

Posted - 05/21/2013 :  05:09:10  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 05/21/2013 :  05:10:48  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000