Post reply

Microsoft SQL Server 2000: Query Analyst - What is wrong with year=q.year?

  • November 14, 2005 at 2:38 pm

    #102928

    Hi all,

    I have the following Table "QTRSALES:

       Year     Quarter     Amount
       -------------------------------
     
       1995     1           125,000.90
       1995     2           136,000.75
       1995     3           212,000.34
       1995     4           328,000.82
       1996     3           728,000.35
       1996     2           422,000.13
       1996     1           328,000.82

    In the Query Analyst of my Microsoft SQL Server 2000 (Developer Edition), I got a syntax error on the code statement "year=q.year" of the following source code: 

    year=q.year,
    SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,
    SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,
    SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,
    SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4
    FROM qtrsales q
    GROUP BY year
    ////////////////////////////////////
    Please tell me what is wrong with the code statement "year=q.year" 
    and how to correct it.
    Thanks in advance,
    Scott  Chang

  • November 14, 2005 at 2:47 pm

    #603771

    My first observation is that the fieldname year is a reserved word in SQL Server. Therefore, you should not name a field that. If you don't have control over changing it, then place the fieldname in square brackets [year] and try to run the script again. The brackets tell SQL Server that what is inside is a fieldname.

    HTH,



    Michelle

  • November 14, 2005 at 4:48 pm

    #603797

    Why not just Q.year, ...?

  • November 15, 2005 at 6:35 am

    #603886

    Scott,

    I ran your code without a problem. I assume that you just left off the SELECT at the beginning of the code because of a cut-and-paste error. If you leave off the SELECT, you get this error:

    Server: Msg 170, Level 15, State 1, Line 11

    Line 11: Incorrect syntax near 'year'.

    CREATE TABLE QTRSALES

    (

      year smallint

    , quarter tinyint

    , amount decimal(9,2)

    )

    GO

    SET NOCOUNT ON

    INSERT QTRSALES (year, quarter, amount) VALUES (1995, 1, 125000.90)

    INSERT QTRSALES (year, quarter, amount) VALUES (1995, 2, 136000.75)

    INSERT QTRSALES (year, quarter, amount) VALUES (1995, 3, 212000.34)

    INSERT QTRSALES (year, quarter, amount) VALUES (1995, 4, 328000.82)

    INSERT QTRSALES (year, quarter, amount) VALUES (1996, 3, 728000.35)

    INSERT QTRSALES (year, quarter, amount) VALUES (1996, 2, 422000.13)

    INSERT QTRSALES (year, quarter, amount) VALUES (1996, 1, 328000.82)

    SET NOCOUNT OFF

    SELECT year=q.year,

    SUM(CASE quarter WHEN 1 THEN amount ELSE 0 END) as Q1,

    SUM(CASE quarter WHEN 2 THEN amount ELSE 0 END) as Q2,

    SUM(CASE quarter WHEN 3 THEN amount ELSE 0 END) as Q3,

    SUM(CASE quarter WHEN 4 THEN amount ELSE 0 END) as Q4

    FROM qtrsales q

    GROUP BY year

     

  • November 17, 2005 at 9:58 am

    #604472

    Hi mkeast, Thanks for your response and wonderful source code.

    Your source code worked nicely in my Microsoft SQL Server 2000 (Developer Edition) Query Analyst. I am a new comer in using SQL Server 2000 and I learned 2 things from your source code: (i) SELECT is needed for "year=q.year" and (ii) Creating Table "QTRSALES" in the beginning of the project. I wonder: (1) Is it  possible to read the input data from Microsoft Office 2003 (Access or Excel) and print the Rotated Table out in a new Table of Access 2003 or Excel 2003 programatically in the Query Analyst.  (2)  Is it possible to use the Access 2003 adp and T-SQL programming for Rotating the Table and Printing the rotated results in Access 2003 or Excel 2003?  Please reply and advise.

    Thanks again,

    Scott  Chang 

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

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