Solved

Incorrect Syntax creating SGL alias column name

Posted on 2010-08-26
10
355 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
execute a MS SQL script as a schedule SQL job 72 125
Delete from table 6 46
How to use TOP 1 in a T-SQL sub-query? 14 42
How can I get this column in my query? 2 40
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
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…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 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

28 Experts available now in Live!

Get 1:1 Help Now