We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
It seems there is a bug with parsing the SQL syntax for this problem; the AS keyword causes a failure when it is perfectly valid. When aliasing the table name "City":
Your comment doesn't actually address the problem. The problem is: using the AS keyword when aliasing a table is perfectly valid T-SQL syntax yet the submission produces errors.
I alias tables regardless of necessity because I feel it is a best practice and also does not affect the performance of the query in any negative way. To your second point, consider the following (which fails): SELECT c.ID FROM City AS c. The alias is referenced in the SELECT query-part which, in terms of the T-SQL interpreter, is compiled after the FROM query-part where the alias is defined.
It's not broken; your syntax is OK for MS SQL but not for Oracle; for Oracle, AS is only valid syntax when aliasing columns and not when aliasing table names. If you were to try
SELECT
c.ID AS i
FROM
City c;
it will execute fine, because that is correct syntax for Oracle. I don't know the specific reason for it (though there are some legitimate arguments out there against using AS), but it's mentioned in many places such as some Oracle forums, techonthenet, and Oracle's SELECT documentation's mentions of
Column Alias:
c_alias
Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.
and Table Alias
t_alias
Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
There is no mention of AS syntax being allowable for t_alias in Oracle's Database SQL Language Reference, and indeed every example they have (in the lang ref for correlated subqueries, subclauses, etc.) uses the syntax:
This all makes sense and I really appreciate you spending the time to reply. Unfortunately, when I wrote the post I did not specify that I was actually using "MS SQL Server" as the language/parser. According to the MSDN documentation for Microsoft Transact-SQL, AS is perfectly valid in a FROM clause to alias a table or view. This is a snippet from their documentation which holds true back to SQL Server 2005:
[ FROM { <table_source> } [ ,...n ] ]
<table_source> ::=
{
table_or_view_name [ [ AS ] table_alias ]
...
I should have been more clear when I commented about this and I apologize. I tested the submission again using the AS keyword and it now works; it would seem that this issue has been resolved since the time I posted. I had submitted a bug to HackerRank so I suppose they finally got to it.
Because I checked it and knew it was working, my assumption was you either didnt know the syntax difference or were accidentally trying to submit a MS SQL solution (due to the TSQL references) as an Oracle solution.
Average Population
You are viewing a single comment's thread. Return to all comments →
It seems there is a bug with parsing the SQL syntax for this problem; the
AS
keyword causes a failure when it is perfectly valid. When aliasing the table name "City":This works
City c
This does not
City AS c
why are you aliasing a table name when it is not required? it will throw an error here because alias name is not being used anywhere later.
Your comment doesn't actually address the problem. The problem is: using the
AS
keyword when aliasing a table is perfectly valid T-SQL syntax yet the submission produces errors.I alias tables regardless of necessity because I feel it is a best practice and also does not affect the performance of the query in any negative way. To your second point, consider the following (which fails):
SELECT c.ID FROM City AS c
. The alias is referenced in theSELECT
query-part which, in terms of the T-SQL interpreter, is compiled after theFROM
query-part where the alias is defined.It's not broken; your syntax is OK for MS SQL but not for Oracle; for Oracle,
AS
is only valid syntax when aliasing columns and not when aliasing table names. If you were to tryit will execute fine, because that is correct syntax for Oracle. I don't know the specific reason for it (though there are some legitimate arguments out there against using
AS
), but it's mentioned in many places such as some Oracle forums, techonthenet, and Oracle's SELECT documentation's mentions of Column Alias:and Table Alias
There is no mention of AS syntax being allowable for t_alias in Oracle's Database SQL Language Reference, and indeed every example they have (in the lang ref for correlated subqueries, subclauses, etc.) uses the syntax:
This all makes sense and I really appreciate you spending the time to reply. Unfortunately, when I wrote the post I did not specify that I was actually using "MS SQL Server" as the language/parser. According to the MSDN documentation for Microsoft Transact-SQL,
AS
is perfectly valid in aFROM
clause to alias a table or view. This is a snippet from their documentation which holds true back to SQL Server 2005:I should have been more clear when I commented about this and I apologize. I tested the submission again using the
AS
keyword and it now works; it would seem that this issue has been resolved since the time I posted. I had submitted a bug to HackerRank so I suppose they finally got to it.Because I checked it and knew it was working, my assumption was you either didnt know the syntax difference or were accidentally trying to submit a MS SQL solution (due to the TSQL references) as an Oracle solution.
Perfectly reasonable assumption; sorry for not being clear the first time around.
My best guess as to the reason is that a table alias is a correlation name, and a correlation name cannot be used for columns (hence the syntax difference): http://docs.oracle.com/javadb/10.8.3.0/ref/rrefcorrelationname.html