9 Replies Latest reply on Aug 18, 2018 9:41 PM by Hari Ankem

    Custom SQL - Incorrect Union Execution

    Austin Burrow

      Hello,

       

      I am attempting to use a Custom SQL Query on an Amazon Redshift Data source, and I can’t understand why my very simple query is executing incorrectly when I attempt to use a union.

       

      In the first section of the Union I am attempting to Sum up a field called [AA], and in the bottom section of the Union I am attempting to sum up a field called [BA].  In MS SQL I would normally solve this issue
      using an Unpivot, but Redshift and Tableau doesn’t seem to want to let me so I am using a Union All as a workaround.

       

       

       

       

      When I attempt to execute the 2 queries below individually, my summed up values are correct.

      When I attempt to Union them in one custom SQL Query, both queries sum up whichever column is used in the top query section.  As opposed to what I want which is the top query summing up [AA] and the bottom query summing up [BA].

       

      (Top Query)

      SELECT DISTINCT

       

      F.[Bus_Unit]

       

      ,'Actual_Amount'             AS           [PvA]

       

      SUM(F.[AA] * -.01)          AS [Amount]

      FROM
      JDE.F0902_co2   F

       

      WHERE

       

      F.[Bus_Unit]      =             ‘42001’

       

      GROUP BY

       

      F.[Bus_Unit]

       

      ,[PvA]

       

       

       

       

      -----------------------------------------------------------------------------------Result

       

      [Bus_Unit]          |              [PvA]                     |              [Amount]

       

      42001                               Actual_Amount                -18,077,000

       

       

       

       

      -----------------------------------------------------------------------------------

      (Bottom Query)

      SELECT DISTINCT

       

      F.[Bus_Unit]

       

      ,'Planned_Amount'         AS           [PvA]

       

      SUM(F.[BA] * -.01)          AS [Amount]

      FROM
      JDE.F0902_co2   F

       

      WHERE

       

      F.[Bus_Unit]      =             ‘42001’

       

      GROUP BY

       

      F.[Bus_Unit]

       

      ,[PvA]

       

       

       

       

       

      -----------------------------------------------------------------------------------Result

       

      [Bus_Unit]          |              [PvA]                     |              [Amount]

       

      42001                                Planned_Amount                            -21,314,000

       

       

       

       

      -----------------------------------------------------------------------------------

       

      (Combined with a Union)

       

      SELECT DISTINCT

       

      F.[Bus_Unit]

       

      ,'Actual_Amount'             AS           [PvA]

       

      SUM(F.[AA] * -.01)          AS [Amount]

      FROM

            
      JDE.F0902_co2   F

       

      WHERE

       

      F.[Bus_Unit]      =             ‘42001’

       

      GROUP BY

       

      F.[Bus_Unit]

       

      ,[PvA]

       

      Union All

       

      SELECT DISTINCT

       

      F.[Bus_Unit]

       

      ,'Planned_Amount'         AS           [PvA]

       

      SUM(F.[BA] * -.01)          AS [Amount]

      FROM

            
      JDE.F0902_co2   F

       

      WHERE

       

      F.[Bus_Unit]      =             ‘42001’

       

      GROUP BY

       

      F.[Bus_Unit]

       

      ,[PvA]

       

      --Result

       

      [Bus_Unit]          |              [PvA]                     |              [Amount]

       

      42001                                    Actual_Amount                -18,077,000

       

      42001                                    Planned_Amount            -18,077,000

       

       

      I have attempted this dozens of times, and at this point I am stumped.  I am very familiar with SQL and have done everything I can think of but the result is always the same.  I’ve double and tripled checked that I didn’t stupidly put [AA] in both queries, I’ve attempted to throw both sections into Sub Queries/temp tables to try to force the queries to execute independently, I’ve attempted to sum up both [AA] and [BA] in both queries so I have identical records (Except the hardcoded [PvA] column) but the result is always the Same: Tableau sums up whichever column is in the top query.

       

       

       

       

      I wouldn’t consider the table I’m using “Huge”, but it is big enough that performance starts to become an issue when I attempt anything that requires me to scan the table more than 2 times.

       

       

       

       

      Please help me, my boss isn’t very technical, so he doesn’t understand why it’s taking me so long to do something as simple as summing up Revenue by Business Unit.  Any advice would be greatly
      appreciated.

       

       

       

      Thanks in advance!

        • 1. Re: Custom SQL - Incorrect Union Execution
          Ken Flerlage

          Are you doing a UNION or a UNION ALL?

          • 2. Re: Custom SQL - Incorrect Union Execution
            Ken Flerlage

            This seems like some sort of bug in Redshift...

            • 3. Re: Custom SQL - Incorrect Union Execution
              Ken Flerlage

              What if you perform the aggregation after the union--so union your underlying details, then use that as a sub-query for performing the aggregation (or even do that aggregation in Tableau).

               

              Another option would probably be to use Tableau Prep to union those two separate queries together and create a Tableau data extract.

              • 4. Re: Custom SQL - Incorrect Union Execution
                Austin Burrow

                Hey Ken,

                 

                Thank you for your response.  I actually had not tried aggregating after the union, but I just took a stab at it and I'm still getting the same results :/

                 

                I don't think it's the aggregation, because when I Try to pull in the data without summing them up, I still get wacky results. (a Different than I was experiencing before, but still wrong)

                 

                 

                SELECT DISTINCT

                 

                F.[Bus_Unit]
                ,F.LedgerDate

                ,F.[AA] * -.01          AS [Actual_Amount]
                ,F.[BA] * -.01          As [Planned_Amount]

                 

                FROM
                JDE.F0902_co2   F

                WHERE

                F.[Bus_Unit]      =             ‘42001’

                 

                -------------------------------------------------------------Result

                Bus_Unit     |     LedgerDate     |     Actual_Amount     |     Planned_Amount

                42001                    08/01/2018               0                                  -758,078

                -------------------------------------------------------------

                 

                SELECT DISTINCT

                F.[Bus_Unit]
                ,F.LedgerDate

                ,F.[AA] * -.01          AS [Actual_Amount]
                ,F.[BA] * -.01          As [Planned_Amount]

                 

                FROM
                JDE.F0902_co2   F

                WHERE

                F.[Bus_Unit]      =             ‘42001’

                 

                UNION ALL
                SELECT DISTINCT

                F.[Bus_Unit]
                ,F.LedgerDate

                ,F.[AA] * -.01          AS [Actual_Amount]
                ,F.[BA] * -.01          As [Planned_Amount]

                 

                FROM
                JDE.F0902_co2   F

                WHERE

                F.[Bus_Unit]      =             ‘42001’

                 

                -------------------------------------------------------------Result

                Bus_Unit     |     LedgerDate     |     Actual_Amount     |     Planned_Amount

                42001                    08/01/2018               -758,078                                 -758,078

                42001                    08/01/2018               0                                       0

                -------------------------------------------------------------

                • 5. Re: Custom SQL - Incorrect Union Execution
                  Ken Flerlage

                  Do you get these same results if you run the query directly against Redshift (i.e. removing Tableau from the picture)? If so, you may need to speak with AWS support.

                  • 6. Re: Custom SQL - Incorrect Union Execution
                    Austin Burrow

                    Hey Ken,


                    In all honesty, this redshift server is really new to everyone in my branch so I don't even know how to Query directly against it.  That was my next idea too so I reached out to the DBA that set it up to help me out.  Admittedly I should have waited for his response to verify that the underlying data isn't the problem, but after seeing that the values populate properly without the Union I was hoping there was a simple solution in Tableau that I was simply overlooking.  

                     

                    You mentioned "Tableau Prep" in a previous response, would you mind elaborating on that  a little more for me?  I looked into using the Union function in Tableau but as far as I can tell I can't union custom queries, and creating a Union with the whole underlying table would cause a host of other issues in other areas of the report.  Especially considering I will have other analyst working on this report and they might overlook the fact that everything's doubled.

                     

                    Thanks again for taking the time to think about this with me, I really appreciate it.

                    • 7. Re: Custom SQL - Incorrect Union Execution
                      Ken Flerlage

                      Tableau Prep is a relatively easy to use data prep tool. You can learn all about it here Tableau Prep | Tableau Software

                       

                      And here are specific details on how to do a union in Prep Connect Tableau Prep to Your Data

                      • 8. Re: Custom SQL - Incorrect Union Execution
                        Ken Flerlage

                        Following up on this one. Let us know how we can help further.

                        • 9. Re: Custom SQL - Incorrect Union Execution
                          Hari Ankem

                          Can you try running the query without the DISTINCT clause included?

                           

                          SELECT[Bus_Unit],
                          'Actual_Amount' AS [PvA],
                          -.01 * SUM([AA]) AS [Amount]
                          FROMJDE.F0902_co2
                          WHERE[Bus_Unit] = '42001'

                          GROUP BY [Bus_Unit],[PvA]

                          UNION ALL

                          SELECT[Bus_Unit],
                          'Planned_Amount' AS [PvA],
                          -.01 * SUM([BA]) AS [Amount]
                          FROMJDE.F0902_co2
                          WHERE[Bus_Unit] = '42001'

                          GROUP BY [Bus_Unit],[PvA]