Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    51

    Question Unanswered: Conditionals on derived columns

    Hi,

    Here's my current query, which throws an error that "AgeCalc" is an invalid column in the WHERE clause:

    ---------------------------------
    SELECT
    .
    .
    .,
    AgeCalc =
    CASE
    WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
    THEN datediff (year, B.DOB, B.DateIn) - 1
    ELSE datediff (year, B.DOB, B.DateIn)
    END

    FROM
    ResidentData B

    WHERE
    (AgeCalc >= 18)
    ---------------------------------

    How do I do conditionals on the "AgeCalc" derived column?

    Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by nbozic

    How do I do conditionals on the "AgeCalc" derived column?

    Thanks.
    You have to write the expression over again:
    WHERE
    CASE
    WHEN dateadd(year, datediff (year, B.DOB, B.DateIn), B.DOB) > B.DateIn
    THEN datediff (year, B.DOB, B.DateIn) - 1
    ELSE datediff (year, B.DOB, B.DateIn)
    END >= 18

    Alternatively, write a view that includes your derived column and then you can use your column name in an expression.

    I don't recommend using CASE statements in WHERE clauses. It can result in sub-optimal query execution plans.

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Mar 2004
    Posts
    51
    Thanks for your help - I will test the solution and see what the performance is like.

    The current situation does not allow me to consider creating views, so I'll have to stick to keeping the query similar to the way it already is.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select * 
      from (
    SELECT ...    
         , AgeCalc =
           CASE WHEN dateadd(year
                           , datediff(year, B.DOB, B.DateIn)
                           , B.DOB) > B.DateIn
                THEN datediff(year, B.DOB, B.DateIn) - 1
                ELSE datediff(year, B.DOB, B.DateIn)
            END
      FROM ResidentData B
           ) as T
     WHERE AgeCalc >= 18
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    51
    Thanks guys. Both solutions worked well. I will use the second one since it's about half a second faster.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •