How to use the AS table???

  • 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.

  • 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

  • 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

  • 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

  • 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.

  • 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.

  • TNX A LOT it works 🙂

    Up to my next Qeustion 🙂

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply