Solved

Incorrect Syntax creating SGL alias column name

Posted on 2010-08-26
10
353 Views
Last Modified: 2012-05-10
I am trying to build a query with alias column names in my ASP.NET application to query the sql database.  These alias names will be used to query out data for display on the website.  I am using CASE but am getting an error:  {"Incorrect syntax near 'A_Flag'."}

I was using AS but had the same error.  What is the correct syntax for setting multiple alias names?

Here is my query:


strSQL = "SELECT DISTINCT PIN,  Form, Status_Flag, CASE Status_Flag " +
                      " WHEN 'A' THEN 1 ELSE 0  'A_Flag' " +
                      " WHEN 'Q' THEN 1 ELSE 0  'Q_Flag' " +
                      " WHEN 'W' THEN 1 ELSE 0  'W_Flag' " +
                      " WHEN 'O' THEN 1 ELSE 0  'O_Flag' " +
                      " WHEN 'D' THEN 1 ELSE 0  'D_Flag' " +
                      " WHEN 'Z' THEN 1 ELSE 0  'Z_Flag' " +
                      " WHEN 'X' THEN 1 ELSE 0  'X_Flag' " +
                      " WHEN 'U' THEN 1 ELSE 0  'U_Flag' " +
                      " FROM t_Answer GROUP BY RecNum, " +
                      " Form, PIN, Status_Flag, A_Flag, L_Flag, Q_Flag, W_Flag, O_Flag, D_Flag," +
                      " Z_Flag, X_Flag, U_Flag;";

ptslv
0
Comment
Question by:ptslv
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 8

Expert Comment

by:mustaccio
ID: 33537108
The correct syntax of the CASE expression is:

CASE status_flag
   WHEN 'A' THEN foo
   WHEN 'B' THEN bar
   ELSE baz
END

The result is a single column. If you need multiple columns you will have to use multiple CASE expressions.
0
 

Author Comment

by:ptslv
ID: 33537283
OK, I changed the Case statement.  Now I get this error:  +            err      {"Invalid column name 'A_Flag'.\r\nInvalid column name 'L_Flag'.\r\nInvalid column name 'Q_Flag'.\r\nInvalid column name 'W_Flag'.\r\nInvalid column name 'O_Flag'.\r\nInvalid column name 'D_Flag'.\r\nInvalid column name 'Z_Flag'.\r\nInvalid column name 'X_Flag'.\r\nInvalid column name 'U_Flag'."}      System.Exception {System.Data.SqlClient.SqlException}

Here is my new statement:

 strSQL = "SELECT DISTINCT PIN,  Form, Status_Flag, CASE Status_Flag " +
                      " WHEN 'A' THEN 1 ELSE 0 END 'A_Flag', " +
                      " CASE Status_Flag WHEN 'Q' THEN 1 ELSE 0 END  'Q_Flag', " +
                      " CASE Status_Flag WHEN 'W' THEN 1 ELSE 0 END  'W_Flag', " +
                      " CASE Status_Flag WHEN 'O' THEN 1 ELSE 0 END  'O_Flag', " +
                      " CASE Status_Flag WHEN 'D' THEN 1 ELSE 0 END  'D_Flag', " +
                      " CASE Status_Flag WHEN 'Z' THEN 1 ELSE 0 END  'Z_Flag', " +
                      " CASE Status_Flag WHEN 'X' THEN 1 ELSE 0 END  'X_Flag', " +
                      " CASE Status_Flag WHEN 'U' THEN 1 ELSE 0 END  'U_Flag' " +
                      " FROM t_Answer GROUP BY RecNum, " +
                      " Form, PIN, Status_Flag, A_Flag, L_Flag, Q_Flag, W_Flag, O_Flag, D_Flag," +
                      " Z_Flag, X_Flag, U_Flag;";
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 33537842
You cannot reference column aliases in a group by statement, you have to reference the column/expression itself something like this.
strSQL = "SELECT DISTINCT PIN,  Form, Status_Flag, CASE Status_Flag " + 

                      " WHEN 'A' THEN 1 ELSE 0  'A_Flag' " +

                      " WHEN 'Q' THEN 1 ELSE 0  'Q_Flag' " + 

                      " WHEN 'W' THEN 1 ELSE 0  'W_Flag' " + 

                      " WHEN 'O' THEN 1 ELSE 0  'O_Flag' " +

                      " WHEN 'D' THEN 1 ELSE 0  'D_Flag' " + 

                      " WHEN 'Z' THEN 1 ELSE 0  'Z_Flag' " +

                      " WHEN 'X' THEN 1 ELSE 0  'X_Flag' " +

                      " WHEN 'U' THEN 1 ELSE 0  'U_Flag' " +

                      " FROM t_Answer GROUP BY RecNum, " +

                      " Form, PIN, Status_Flag, CASE Status_Flag " + 

                      " WHEN 'A' THEN 1 ELSE 0 " +

                      " WHEN 'Q' THEN 1 ELSE 0 " + 

                      " WHEN 'W' THEN 1 ELSE 0 " + 

                      " WHEN 'O' THEN 1 ELSE 0 " +

                      " WHEN 'D' THEN 1 ELSE 0 " + 

                      " WHEN 'Z' THEN 1 ELSE 0 " +

                      " WHEN 'X' THEN 1 ELSE 0" +

                      " WHEN 'U' THEN 1 ELSE 0;";

Open in new window

0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 250 total points
ID: 33537933
Sorry, I copied from the original instead of your modified query which had better syntax, try this one instead.
strSQL = "SELECT DISTINCT PIN,  Form, Status_Flag, " + 

                      " CASE Status_Flag WHEN 'A' THEN 1 ELSE 0 END  'A_Flag', " +

                      " CASE Status_Flag WHEN 'Q' THEN 1 ELSE 0 END  'Q_Flag', " +

                      " CASE Status_Flag WHEN 'W' THEN 1 ELSE 0 END  'W_Flag', " +

                      " CASE Status_Flag WHEN 'O' THEN 1 ELSE 0 END  'O_Flag', " +

                      " CASE Status_Flag WHEN 'D' THEN 1 ELSE 0 END  'D_Flag', " +

                      " CASE Status_Flag WHEN 'Z' THEN 1 ELSE 0 END  'Z_Flag', " +

                      " CASE Status_Flag WHEN 'X' THEN 1 ELSE 0 END  'X_Flag', " +

                      " CASE Status_Flag WHEN 'U' THEN 1 ELSE 0 END  'U_Flag' " +

                      " FROM t_Answer GROUP BY RecNum, " +

                      " Form, PIN, Status_Flag, " +

                      " CASE Status_Flag WHEN 'A' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'Q' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'W' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'O' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'D' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'Z' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'X' THEN 1 ELSE 0 END , " +

                      " CASE Status_Flag WHEN 'U' THEN 1 ELSE 0 END  " +

                      " ;";

Open in new window

0
 

Author Comment

by:ptslv
ID: 33542139
I set it up this way and am still getting an error:  {"Incorrect syntax near the keyword 'CASE'."}
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 33542166
this should work better:
strSQL = "SELECT PIN,  Form, Status_Flag " + 

                      " ,CASE Status_Flag WHEN 'A' THEN 1 ELSE 0 END  [A_Flag] " +

                      " ,CASE Status_Flag WHEN 'Q' THEN 1 ELSE 0 END  [Q_Flag] " +

                      " ,CASE Status_Flag WHEN 'W' THEN 1 ELSE 0 END  [W_Flag] " +

                      " ,CASE Status_Flag WHEN 'O' THEN 1 ELSE 0 END  [O_Flag] " +

                      " ,CASE Status_Flag WHEN 'D' THEN 1 ELSE 0 END  [D_Flag] " +

                      " ,CASE Status_Flag WHEN 'Z' THEN 1 ELSE 0 END  [Z_Flag] " +

                      " ,CASE Status_Flag WHEN 'X' THEN 1 ELSE 0 END  [X_Flag] " +

                      " ,CASE Status_Flag WHEN 'U' THEN 1 ELSE 0 END  [U_Flag] " +

                      " FROM t_Answer GROUP BY RecNum, Form, PIN, Status_Flag " +

                      " ,CASE Status_Flag WHEN 'A' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'Q' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'W' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'O' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'D' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'Z' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'X' THEN 1 ELSE 0 END  " +

                      " ,CASE Status_Flag WHEN 'U' THEN 1 ELSE 0 END  " +

                      " ";

Open in new window

0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 33542348
@angelIII, why would the bracketed aliases rather than quoted ones make a difference?  The quoted ones worked when I tested the query.  I think they missed a comma or something in applying the sample.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33542366
CGLutterell: eventually because of this setting:
http://msdn.microsoft.com/en-us/library/ms174393.aspx
0
 

Author Closing Comment

by:ptslv
ID: 33542581
I was missing a comment, but had another error, so I put the brackets around the fields.  Thank you both for the quick help!

ptslv
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 33545021
Thanks ptslv, glad we could help you.
@angelIII, I was not aware of that with the R2 release, thanks for that insight.  I always value your knowledgable advice.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now