1 2 3 Previous Next 32 Replies Latest reply on Nov 17, 2015 9:00 AM by James Carter Go to original post
      • 30. Re: Tableau & SQL Group
        James Carter

        I can not figure out the error in my syntax for the following custom SQL UNION query (as pasted below, attached, and with Excel source attached too). Thanks in advance for your help!

         

        SELECT [PortfolioMappedLatLong$].[Corg] AS [Corg],

        [PortfolioMappedLatLong$].[PHB Project ID] AS [PHB Project ID],

        [PortfolioMappedLatLong$].[Project Name] AS [Project Name],

        [PortfolioMappedLatLong$].[Address] AS [Address],

        [PortfolioMappedLatLong$].[Type] AS [Type],

        [PortfolioMappedLatLong$].[Total Units] AS [Total Units],

        [PortfolioMappedLatLong$].[Regulated Units] AS [Regulated Units],

        [PortfolioMappedLatLong$].[0-30%MFI Units] AS [0-30%MFI Units],

        [PortfolioMappedLatLong$].[31-50% MFI Units] AS [31-50% MFI Units],

        [PortfolioMappedLatLong$].[51-80% MFI Units] AS [51-80% MFI Units],

        [PortfolioMappedLatLong$].[Latitude] AS [Latitude],

        [PortfolioMappedLatLong$].[Longitude] AS [Longitude],

        [PortfolioMappedLatLong$].[Completion Date] AS [Completion Date],

        NULL AS [OBJECTID],

        NULL AS [AREA],

        NULL AS [PERIMETER],

        NULL AS [COALIT],

        NULL AS [SHARED],

        NULL AS [NAME],

        NULL AS [ACRES],

        NULL AS [Percent],

        NULL AS [Shape_Leng],

        NULL AS [PolygonId],

        NULL AS [PointId]

        FROM [PortfolioMappedLatLong$]

         

        UNION

         

        SELECT [Financial Planning District$].[Corg] as [Corg],

        NULL AS [PHB Project ID],

        NULL AS [Project Name],

        NULL AS [Address],

        NULL AS [Type],

        NULL AS [Total Units],

        NULL AS [Regulated Units],

        NULL AS [0-30%MFI Units],

        NULL AS [31-50% MFI Units],

        NULL AS [51-80% MFI Units],

        [Financial Planning District$].[Latitude] AS [Latitude],

        [Financial Planning District$].[Longitude] AS [Longitude],

        NULL AS [Completion Date],

        [Financial Planning District$].[OBJECTID] AS [OBJECTID],

        [Financial Planning District$].[AREA] AS [AREA],

        [Financial Planning District$].[PERIMETER] AS [PERIMETER],

        [Financial Planning District$].[COALIT] AS [COALIT],

        [Financial Planning District$].[SHARED] AS [SHARED],

        [Financial Planning District$].[NAME] AS [NAME],

        [Financial Planning District$].[ACRES] As [ACRES],

        [Financial Planning District$].[Percent] AS [Percent],

        [Financial Planning District$].[Shape_Leng] AS [Shape_Leng],

        [Financial Planning District$].[PolygonId] AS [PolygonId],

        [Financial Planning District$].[PointId] AS [PointId]

        FROM [Financial Planning District$]

        • 31. Re: Tableau & SQL Group
          Shawn Wallwork

          Your types of data, and number/order of columns don't match up. In a UNION both worksheets have to match one-to-one column wise. Here are yours:

           

          2015-11-17_8-39-52.png

           

          --Shawn

           

          EDIT: Are you sure you're not looking for a JOIN?

          • 32. Re: Tableau & SQL Group
            James Carter

            Thanks for your reply, Shawn.

             

            In the SQL query, I used NULLS AS [Column] for those Columns that existed one sheet but not the other. I think this approach created an outer JOIN, I think. . .

             

            I think I got the desired Tableau workbook output I was seeking. My goals was the have points (lat/long) as a shape mark (green circles) on a custom polygon shape map (8 shapes).

             

            map HC LOS.jpg

            1 2 3 Previous Next