CODEWITHSUNDEEP
sqlsql-server
Oualid
Oualid

Reputation: 403

sql query - Invalid column name

I get an error when I execute the following sql query.

SELECT 
    RTRIM(name) AS [Segment Name], 
    growth,groupid AS [Group Id], 
    filename AS [File   Name],
    CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
    CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
    CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
    CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent_Used]
FROM 
    sysfiles
WHERE 
    growth = 0 AND  Percent_Used > 60
ORDER BY 
    groupid DESC

The error says:

Msg 207, Level 16, State 1, Line 7
Invalid column name 'Percent_Used'.

Why do I get this error??

Upvotes: 0

Views: 5507

Answers (3)

bjnr
bjnr

Reputation: 3437

WITH cte
AS
(
SELECT 
    RTRIM(name) AS [Segment Name], 
    growth,groupid AS [Group Id], 
    filename AS [File   Name],
    CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
    CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
    CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
    CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent_Used]
FROM 
    sysfiles
)
SELECT *
FROM cte
WHERE 
    growth = 0 
    AND  Percent_Used > 60
ORDER BY [Group Id] DESC

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

If you see logical order processing of query where clause is evaluated before select. to make it work you need to use derived table concept.

1. FROM
2. ON
3. OUTER
4. **WHERE**
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. **SELECT**

http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/ 

   Select * from
    (
        SELECT 
            RTRIM(name) AS [Segment Name], 
            growth,groupid AS [Group Id], 
            filename AS [File   Name],
            CAST(size/128.0 AS DECIMAL(10,2)) AS [Size in MB],
            CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2)) AS [Space Used],
            CAST(size/128.0-(FILEPROPERTY(name, 'SpaceUsed')/128.0) AS DECIMAL(10,2)) AS [Available Space],
            CAST((CAST(FILEPROPERTY(name, 'SpaceUsed')/128.0 AS DECIMAL(10,2))/CAST(size/128.0 AS DECIMAL(10,2)))*100 AS DECIMAL(10,2)) AS [Percent_Used]
        FROM 
            sysfiles
    ) DT

        WHERE growth = 0 AND  Percent_Used > 60
        ORDER BY groupid DESC

Upvotes: 5

David Pilkington
David Pilkington

Reputation: 13618

You are trying to use a column you have defined to filter in the WHERE clause. This column does not exist in the table sysfiles

If you want you can wrap that SELECT in another SELECT and do the filtering on the latter SELECT. That way the column already "exists".

Upvotes: 3

Related Questions