none
MSG 107

    Question

  • USE UMDW
    GO

    DECLARE @TERM AS char(4)

    SET @TERM = '1133'

    SELECT DISTINCT a.EMPLID,
    a.GRADE_POINTS,
    a.UNT_TAKEN_GPA
    INTO #COHORT_LIST
    FROM PS_DWSA_ST_CAR_TRM a INNER JOIN playground.dbo.AY_GPA_COHORT_1213 b
    ON a.EMPLID = b.EMPLID
    AND a.ACAD_CAREER = b.ACAD_CAREER
    WHERE a.TERM = @TERM
     AND a.INSTITUTION = 'UMNTC'
     AND b.SPRING = 'Y'

    SELECT DISTINCT EMPLID,
    SUM(a.GRADE_POINTS)/SUM(a.UNT_TAKEN_GPA) AS TERM_GPA
    FROM #COHORT_LIST
    WHERE GRADE_POINTS > 0.0
    GROUP BY EMPLID
    HAVING SUM(a.GRADE_POINTS)/SUM(a.UNT_TAKEN_GPA) > 2.9999
    ORDER BY 1

    DROP TABLE #COHORT_LIST

    When I execute the query I get these error message. Can someone help me identify the problem

    Msg 107, Level 16, State 2, Line 17
    The column prefix 'a' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 2, Line 17
    The column prefix 'a' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 2, Line 17
    The column prefix 'a' does not match with a table name or alias name used in the query.
    Msg 107, Level 16, State 2, Line 17
    The column prefix 'a' does not match with a table name or alias name used in the query.

      Thanks
    Wednesday, September 04, 2013 4:56 PM

Answers

All replies

  • SELECT DISTINCT EMPLID,
    SUM(a.GRADE_POINTS)/SUM(a.UNT_TAKEN_GPA) AS TERM_GPA
    FROM #COHORT_LIST
    WHERE GRADE_POINTS > 0.0
    GROUP BY EMPLID
    HAVING SUM(a.GRADE_POINTS)/SUM(a.UNT_TAKEN_GPA) > 2.9999
    ORDER BY 1

    Hello Apkar,

    In your second query you are using the alias "a" in the select list, but in the FROM clause you haven't declare an alias "a" =>

    FROM #COHORT_LIST AS a


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Sarat Babu (SS) Wednesday, September 04, 2013 5:34 PM
    Wednesday, September 04, 2013 5:09 PM
  • Olaf,

    Thank you for the suggestion. when I declared alias in the from clause as you suggested, I am getting this error

    Msg 2714, Level 16, State 6, Line 6
    There is already an object named '#COHORT_LIST' in the database.

    Wednesday, September 04, 2013 5:54 PM
  • BEGIN
    IF OBJECT_ID('tempdb..#COHORT_LIST') IS NOT NULL
    DROP TABLE #COHORT_LIST
    DECLARE @TERM AS char(4)
    SET @TERM = '1133'
    SELECT DISTINCT a.EMPLID,
    a.GRADE_POINTS,
    a.UNT_TAKEN_GPA
    INTO #COHORT_LIST
    FROM PS_DWSA_ST_CAR_TRM a INNER JOIN playground.dbo.AY_GPA_COHORT_1213 b
    ON a.EMPLID = b.EMPLID
    AND a.ACAD_CAREER = b.ACAD_CAREER
    WHERE a.TERM = @TERM
     AND a.INSTITUTION = 'UMNTC'
     AND b.SPRING = 'Y'
    SELECT DISTINCT EMPLID,
    SUM(GRADE_POINTS)/SUM(UNT_TAKEN_GPA) AS TERM_GPA
    FROM #COHORT_LIST 
    WHERE GRADE_POINTS > 0.0
    GROUP BY EMPLID
    HAVING SUM(GRADE_POINTS)/SUM(UNT_TAKEN_GPA) > 2.9999
    ORDER BY 1
    DROP TABLE #COHORT_LIST
    END

    Wednesday, September 04, 2013 6:49 PM
    Moderator
  • Jingyang,

    Thank you. This query did not show any error but it only spit out the header

    Emplid Term_GPA

    It did not show any data under the hearder

    Thanks


    Wednesday, September 04, 2013 7:11 PM
  • Try this query to see whether you have any rows returned:

    DECLARE @TERM AS char(4)
    SET @TERM = '1133'
    SELECT  a.EMPLID, SUM(a.GRADE_POINTS)*1.0/SUM(a.UNT_TAKEN_GPA) AS TERM_GPA
    FROM PS_DWSA_ST_CAR_TRM a INNER JOIN playground.dbo.AY_GPA_COHORT_1213 b
    ON a.EMPLID = b.EMPLID
    AND a.ACAD_CAREER = b.ACAD_CAREER
    WHERE a.TERM = @TERM
     AND a.INSTITUTION = 'UMNTC'
     AND b.SPRING = 'Y'
     GROUP BY a.EMPLID
    HAVING SUM(a.GRADE_POINTS)/SUM(a.UNT_TAKEN_GPA) > 2.9999
    ORDER BY a.EMPLID

    Wednesday, September 04, 2013 7:21 PM
    Moderator
  • Msg 8134, Level 16, State 1, Line 3

    Divide by zero error encountered.


    Wednesday, September 04, 2013 8:33 PM
  • You need to use a CASE to handle the 0 value returned for your query.

    Try to change your filter to see whether you can get some results first.

    Wednesday, September 04, 2013 8:40 PM
    Moderator