Solved

Incorrect Syntax creating SGL alias column name

Posted on 2010-08-26
10
367 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 27

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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 27

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 143

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 27

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 143

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 27

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

724 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