Post reply

Calculating on a field with numbers and Text .. uhhgg

  • December 8, 2005 at 3:44 pm

    #167382

    I have a query designed to identify records with a birthdate more than 3 years +- the median, calculated by creating an aliased field containing the result of adding the number 6 to the value in the grade field.   The problem is, we have kindergarten students flagged as K1, K2, etc.   My script (A. below) works great till it gets to the K students, then it doesn't work unless I filter out (as I did with A) the K students,  Problem is, I need to do this calculation on them too.  In script B. Below, you'll see my effort to include them by inserting a "case" statement to convert in an aliased field (not the underlying data.. can't do that) the K1 to -1 and the K2 to 0 and then doing the final calculation, but haven't been able to get it to work....  I get the errors...

    Server: Msg 207, Level 16, State 3, Line 2

    Invalid column name 'Grade'.

    Server: Msg 207, Level 16, State 1, Line 2

    Invalid column name 'Grade'.

    Server: Msg 207, Level 16, State 1, Line 2

    Invalid column name 'grade'.

    Here is the script A that works, but does'nt include the K students

    SELECT     FIRSTNAME, LASTNAME, PERMNUM, BIRTHDATE, SCHOOLNUM,  CAST(GRADE AS char(2)) AS Grade, GRADE + 6 AS PAge, DATEDIFF(year,

                          BIRTHDATE, GETDATE()) AS ActAge, DATEDIFF(year, BIRTHDATE, GETDATE()) - GRADE - 6 AS Differential

    FROM         dbo.ASTUALL

    WHERE     (GRADE <> 'K') AND (GRADE <> 'K2') AND (GRADE <> 'K1') AND (NOT (DATEDIFF(year, BIRTHDATE, GETDATE()) - GRADE - 6 BETWEEN - 3 AND 3))

     

    Here is the script B that tries to include the K's ... without success...

    USE CONSASTU

    Go

    SELECT  Grade =

     (CASE 

            WHEN Grade = 'k1' THEN '-1'

            WHEN Grade = 'k2' THEN '0'

            ELSE grade

     end)

    Select cast (Grade as int)

    status,firstname,lastname,permnum,birthdate,schoolnum

    (grade + 6) as PGrade

    FROM ASTUALL

    WHERE  (NOT (DATEDIFF(year, BIRTHDATE, GETDATE()) - (GRADE + 6) BETWEEN - 3 AND 3))

    order by Grade asc

    And the error I get...

    Server: Msg 195, Level 15, State 10, Line 10

    'schoolnum' is not a recognized function name.

    Thanks in advance for your help! 

     

  • December 8, 2005 at 4:00 pm

    #608560

    need a comma after schoolnum, before (grade +)

  • December 8, 2005 at 4:10 pm

    #608561

    need a comma after schoolnum, before (grade +)

  • December 9, 2005 at 7:24 am

    #608647

    You can't refer to a column alias in the where clause.  I also don't understand why you have a couple of selects in your second attempt?  So modify your query to be

    SELECT  
 cast(CASE  
        WHEN Grade = 'k1' THEN '-1'
        WHEN Grade = 'k2' THEN '0'
        ELSE grade 
        end as int) as Grade,
status,firstname,lastname,permnum,birthdate,schoolnum,
 cast(CASE  
        WHEN Grade = 'k1' THEN '-1'
        WHEN Grade = 'k2' THEN '0'
        ELSE grade 
        end as int) + 6 as PGrade
FROM ASTUALL
WHERE  (NOT (DATEDIFF(year, BIRTHDATE, GETDATE()) - 
(


cast(CASE  
        WHEN Grade = 'k1' THEN '-1'
        WHEN Grade = 'k2' THEN '0'
        ELSE grade 
        end as int)
) BETWEEN - 3 AND 3))
order by Grade asc

    Not sure if you can use the column alias in the order by or not - I think you might be able to.  To avoid the duplication of code, you could make a user-defined function that accepted a char(2) and returned an integer.  Look in Books Online for more info.

    Good luck!

  • December 9, 2005 at 7:40 am

    #608653

    This SQL will become considerably simpler, if you calculate the Grade column in one select, and then you can refer to it the way you tried and perform the operations on the computed column. Something like this (based on Ian's SQL in previous post... though I'm not sure that this particular code does what you need, it should be illustrative enough to show what I mean) :

    SELECT a.grade, a.status, a.firstname, a.lastname, a.permnum, a.birthdate, a.schoolnum, a.grade + 6 as PGrade

    FROM

    (SELECT cast(CASE WHEN Grade = 'k1' THEN '-1'

             WHEN Grade = 'k2' THEN '0'       

      ELSE grade end as int) as grade,

    status,firstname,lastname,permnum,birthdate,schoolnum

    FROM ASTUALL) as a

    WHERE (NOT (DATEDIFF(year, BIRTHDATE, GETDATE()) - a.grade) BETWEEN - 3 AND 3)

    ORDER BY a.grade

    HTH, Vladan

    /*edit - added back a part of SQL lost when copying*/

Viewing 5 posts - 1 through 4 (of 4 total)

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