Incorrect Syntax creating SGL alias column name

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
ptslvAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
mustaccioCommented:
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
 
ptslvAuthor Commented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Chris LuttrellSenior Database ArchitectCommented:
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
 
Chris LuttrellConnect With a Mentor Senior Database ArchitectCommented:
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
 
ptslvAuthor Commented:
I set it up this way and am still getting an error:  {"Incorrect syntax near the keyword 'CASE'."}
0
 
Chris LuttrellSenior Database ArchitectCommented:
@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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
CGLutterell: eventually because of this setting:
http://msdn.microsoft.com/en-us/library/ms174393.aspx
0
 
ptslvAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.