5 Replies Latest reply on Apr 12, 2019 3:19 PM by Steven Aultman

    "Column ambiguously defined" ... not really!

    Mike DLoss

      Ok, so I'm trying to run a custom SQL query for a data connection, something like this:

       

      select col1, col1, col2 from usertest1, usertest2

       

      Both of these tables have both of these columns. And when previewing results, it gives me this error Oracle database error 918: ORA-00918: column ambiguously defined. Ok, I get that, the error makes sense.

       

      But now, when I try this query:

       

      select usertest1.col1, usertest2.col1, usertest2.col2 from usertest1, usertest2

       

      I am still receiving this error: Oracle database error 918: ORA-00918: column ambiguously defined.

       

      Anyone have insight as to why this is happening and how I can avoid it?

        • 1. Re: "Column ambiguously defined" ... not really!
          Aaron Clancy

          Tableau will remove the table prefix from the name and be left with col1 and col1 (Which is not allowed)

          You need to alias the fields names (With names that aren't "exactly" then same).

           

          select usertest1.col1 as 1_col1, usertest2.col1 as 2_col1, usertest2.col2 from usertest1, usertest2

          4 of 4 people found this helpful
          • 2. Re: "Column ambiguously defined" ... not really!
            Mike DLoss

            Worked great, thanks!

            • 3. Re: "Column ambiguously defined" ... not really!
              Konstantinos Drosatos

              and what if you want to left join the tables based on these fields? You can't use alias names, so you are again facing the same issue.

              • 4. Re: "Column ambiguously defined" ... not really!
                Konstantinos Drosatos

                if you alias the columns but you need to use them as common fields to join the tables then it doesn't work. The Alias names (LEFT JOIN ON Alias1=Alias2 )are not identified. So you need to use the original column names (Table1.Samename=Table2.Samename) which returns the original error...

                • 5. Re: "Column ambiguously defined" ... not really!
                  Steven Aultman

                  I was getting this same error but for a different reason, and I think mentioning it here might prove valuable to someone down the road.

                   

                  I was building a Tableau data source using SQL tested in Oracle SQL Developer, then copied and pasted into Tableau. It's a long query and I had copied a few columns from the select list to a spot further down, in order to group similar things together (physically, in the file, not in the SQL sense). But instead of cutting and pasting, I had copied and pasted, so I had multiple instances of the same exact table.column_name in the query. Oracle SQL Developer has no problem with this. Tableau won't have it. But since the query executes in Oracle just fine, how are you supposed to debug the problem?

                   

                  Answer: Copy your SQL into a different file in Oracle, make a few changes to it if needed (I had to remove an ORDER BY section), and try to CREATE a VIEW. That will engage Oracle's more detailed error-checking until you can find and eliminate the bugs. This may mirror what Tableau is doing behind the scenes, i.e. creating a view from the SQL.

                   

                  Hope that helps!

                  1 of 1 people found this helpful