4 Replies Latest reply on Apr 20, 2016 1:12 AM by Tony Ball

    Postgre SQL Query misbehaving in Tableau Custom SQL

    Tony Ball

      Hi All,

       

      I have an interesting problem with some custom SQL written to query Postgre SQL database. The query itself is fine and executes without issue when ran outside of Tableau.

      However I get an error regarding columns being ambiguous when ran in Tableau. I think the issue is that I have ItemName column from the same table twice in the query. However the ItemName references have been clearly aliased?

       

      Has anyone encountered an issue like this before and is there a workaround?

       

      Query:

       

      select loc."LocationCode", src_item."ItemName",
      src_port."PortName", dst_item."ItemName",
      dst_port."PortName" from  odbc."dcPowerConnections"
      conn

      join odbc."dcPowerPorts" src_port on
      conn."SourcePortID" = src_port."PowerPortID"

      join odbc."dcAllItems" src_item on src_port."ItemID"
      = src_item."ItemID"

      join odbc."dcPowerPorts" dst_port on
      conn."DestinationPortID" = dst_port."PowerPortID"

      join odbc."dcAllItems" dst_item on dst_port."ItemID"
      = dst_item."ItemID"

      join odbc."dcRooms" loc on src_item."RoomID" =
      loc."ID"

      where conn."DestinationPortID" in

      (

      select a."PowerPortID"

      from odbc."dcPowerPorts" a

      join odbc."dcAllItems" b on a."ItemID" =
      b."ItemID"

      where b."Class" ilike 'Power Outlet'

      and b."Status" in ('Planned', 'Installed')

      and a."Used" = TRUE

      )

      order by loc."LocationCode", src_item."ItemName",
      src_port."PortName", dst_item."ItemName",
      dst_port."PortName"

       

       

      Screenshot of error attached.

       

      Would really appreciate any help on this one. Thanks.

        • 1. Re: Postgre SQL Query misbehaving in Tableau Custom SQL
          Tharashasank Davuluru

          Hi Tony,

           

          If your SQL query references duplicate columns, you may get errors when trying to use one of the columns in Tableau. This will happen even if the query is valid. For example, consider the following query:

           

          SELECT * from authors, titleauthor where authors.au_id = titleauthor.au_id

           

          The query is valid, but the au_id field is ambiguous because it exists in both the “authors” table and the “titleauthor” table. Tableau will connect to the query but you will get an error anytime you try to use the au_id field. That’s because Tableau doesn’t know which table you are referring to.

          1 of 1 people found this helpful
          • 2. Re: Postgre SQL Query misbehaving in Tableau Custom SQL
            Venkata P

            Hi Tony ,

             

            even if you referenced right tables you need to specify different columns names .

             

            if you below two columns both output column names are same .

            loc."LocationCode", src_item."ItemName" ,

            src_port."PortName", dst_item."ItemName",

             

             

            can you run below query and see .

             

            select loc."LocationCode", src_item."ItemName" as Sitemname,
            src_port."PortName", dst_item."ItemName",
            dst_port."PortName" from  odbc."dcPowerConnections"
            conn

            join odbc."dcPowerPorts" src_port on
            conn."SourcePortID" = src_port."PowerPortID"

            join odbc."dcAllItems" src_item on src_port."ItemID"
            = src_item."ItemID"

            join odbc."dcPowerPorts" dst_port on
            conn."DestinationPortID" = dst_port."PowerPortID"

            join odbc."dcAllItems" dst_item on dst_port."ItemID"
            = dst_item."ItemID"

            join odbc."dcRooms" loc on src_item."RoomID" =
            loc."ID"

            where conn."DestinationPortID" in

            (

            select a."PowerPortID"

            from odbc."dcPowerPorts" a

            join odbc."dcAllItems" b on a."ItemID" =
            b."ItemID"

            where b."Class" ilike 'Power Outlet'

            and b."Status" in ('Planned', 'Installed')

            and a."Used" = TRUE

            )

            order by loc."LocationCode", src_item."ItemName",
            src_port."PortName", dst_item."ItemName",
            dst_port."PortName"

            1 of 1 people found this helpful
            • 3. Re: Postgre SQL Query misbehaving in Tableau Custom SQL
              Andrew Watson

              You have 2 columns with the same name, ItemName, hence the error

              1 of 1 people found this helpful
              • 4. Re: Postgre SQL Query misbehaving in Tableau Custom SQL
                Tony Ball

                Thank You! I had to ailas one of the port names too but all works perfectly now. Much appreciated.