When I enter a query into ADS which contains a join and the tables have common column names, the database server will not execute the query if the column names are not identified with their proper namespaces.
But if I execute such a query in the Execute/Edit mode, a pop up window appears and asks me to disambiguate the columns again. This is time consuming and quite tedious if you have a complex query with a lot of columns.
Response
Sachin Prakash over 10 years ago
ADS has to prompt due to technical limits by the database vendors. When you type in a query into ADS and “execute” it, ADS does not parse the query. Instead, it just looks for the start and end blocks of the query and sends that to the database server. The database server then parses the query, executes it and returns back to ADS the resulting dataset. Here comes the limitation: In the resulting dataset, the database vendors don’t specify the table name of each column. They only provide the column names. So, for each returned column, ADS has to determine whether that column name exists in more than 1 table. If so, ADS flags it as ambiguous and asks the user to disambiguate.
One possible workaround for you is to disable prompting of ambiguous columns. By disabling, ADS will automatically make ambiguous columns read-only in Table Data Editor. This will only streamline things for you if you don’t need to edit the ambiguous column values. This option can be toggled by going to: File -> Options -> Table Data Editor -> Prompt for Ambiguous Columns