Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    83

    Unanswered: invalid identifier error

    I get an "invalid identifier" error when I try to sum two alias values.

    For example this works. I am grouping 4 departments into two groups:

    SELECT DISTINCT SUM(dept1) as univ_dept1,
    SUM(dept2) as univ_dept2, SUM(dept3) as univ_dept3,
    SUM(dept4) as univ_dept4,
    Coalesce(Sum(dept1), 0)
    + Coalesce(Sum(dept2), 0) AS student_total,
    Coalesce(Sum(dept3), 0)
    + Coalesce(Sum(dept4), 0) AS staff_total


    BUT when I want to group the two GROUPINGS together, I get the error:

    Coalesce(Sum(student_total), 0)
    + Coalesce(Sum(staff_total), 0) AS univ_total

    So why can't I reference my alias'd groupings like that?

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    You can not use the alias in the select clause.

    But you can use an "in line view"

    Code:
    SELECT univ_dept1, univ_dept2, univ_dept3, univ_dept4,
               coalesce(Sudent_total), coalesce(staff_total)
    FROM (SELECT DISTINCT SUM(dept1) as univ_dept1,
                         SUM(dept2) as univ_dept2, 
                         SUM(dept3) as univ_dept3, 
                         SUM(dept4) as univ_dept4,
                         Coalesce(Sum(dept1), 0) + 
                               Coalesce(Sum(dept2), 0) AS student_total,
                         Coalesce(Sum(dept3), 0) + 
                               Coalesce(Sum(dept4), 0) AS staff_total
              FROM table_name
              WHERE [conditions] )

Posting Permissions

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