Solved

Incorrect Syntax creating SGL alias column name

Posted on 2010-08-26
10
360 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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
 
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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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