ptslv
asked on
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
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
ASKER
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.Sql Exception}
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;";
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;";
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;";
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I set it up this way and am still getting an error: {"Incorrect syntax near the keyword 'CASE'."}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@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.
CGLutterell: eventually because of this setting:
http://msdn.microsoft.com/en-us/library/ms174393.aspx
http://msdn.microsoft.com/en-us/library/ms174393.aspx
ASKER
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
ptslv
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.
@angelIII, I was not aware of that with the R2 release, thanks for that insight. I always value your knowledgable advice.
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.