13 Replies Latest reply on Aug 2, 2013 7:21 PM by Richard Leeke

    Custom SQL Union and Union All problems. Query too complex!

    Ben Eddings

      Currently, some of our traffic tracking is done by people manually pulling reports and copying and pasting into excel. With our recent implementation of Omniture SiteCatalyst, and specifically ReportBuilder, I can now pull the stats into an excel file for use as a data source for a tableau workbook that can be refreshed daily at the click of a button. Well, two buttons.

       

      In this file I have around 100 worksheets (6 boards multiplied by about 19 channels. This is necessary for the way ReportBuilder outputs) and need to Union each sheet, so in effect stacking them all on top of each other.

       

      The obvious way to join two sheets would be...

       

      SELECT * FROM ['TJ PPC$']

      UNION ALL

      SELECT * FROM ['TJ PPC B$']

      UNION ALL

      SELECT * FROM ['TJ PPC Camps$']


      ...but no matter what I tried, I couldn't make it work. Apparently The number of columns in the two selected tables or queries of a union query do not match. I'm not sure if blank columns on some sheets are the cause. All sheets have the same number of columns and matching column titles, but not necessarily in the same order or with data on every sheet.


      When phrased like this however (with the added benefit of allowing me to specify the channel and board of each worksheet) it worked...

       

      SELECT

          [TJ PPC$].[Aggregator] AS [Aggregator],

          [TJ PPC$].[Day] AS [Day],

          [TJ PPC$].[Visits] AS [Visits],

          [TJ PPC$].[Applications] AS [Applications],

          [TJ PPC$].[New Applicants] AS [New Applicants],

          [TJ PPC$].[New Email Addresses] AS [New Email Addresses],

          [TJ PPC$].[Unique Visitors] AS [Unique Visitors],

          [TJ PPC$].[Job Detail Views] AS [Job Detail Views],

          [TJ PPC$].[CV Captures] AS [CV Captures],

          [TJ PPC$].[Clicks] AS [Clicks],

          [TJ PPC$].[New Visitors] AS [New Visitors],

          [TJ PPC$].[Unique Applicants] AS [Unique Applicants],

          [TJ PPC$].[Priority Applications] AS [Priority Applications],

          [TJ PPC$].[Campaign] AS [Campaign],

          'Paid Search' AS [Channel],

          'TotalJobs' AS [Board]

      FROM [TJ PPC$]

      UNION

      SELECT

          [TJ PPC Camps$].[Aggregator] AS [Aggregator],

          [TJ PPC Camps$].[Day] AS [Day],

          [TJ PPC Camps$].[Visits] AS [Visits],

          [TJ PPC Camps$].[Applications] AS [Applications],

          [TJ PPC Camps$].[New Applicants] AS [New Applicants],

          [TJ PPC Camps$].[New Email Addresses] AS [New Email Addresses],

          [TJ PPC Camps$].[Unique Visitors] AS [Unique Visitors],

          [TJ PPC Camps$].[Job Detail Views] AS [Job Detail Views],

          [TJ PPC Camps$].[CV Captures] AS [CV Captures],

          [TJ PPC Camps$].[Clicks] AS [Clicks],

          [TJ PPC Camps$].[New Visitors] AS [New Visitors],

          [TJ PPC Camps$].[Unique Applicants] AS [Unique Applicants],

          [TJ PPC Camps$].[Priority Applications] AS [Priority Applications],

          [TJ PPC Camps$].[Campaign] AS [Campaign],

          'Paid Search Campaigns' AS [Channel],

          'TotalJobs' AS [Board]

      FROM [TJ PPC Camps$]

      UNION

      SELECT

          [TJ PPC B$].[Aggregator] AS [Aggregator],

          [TJ PPC B$].[Day] AS [Day],

          [TJ PPC B$].[Visits] AS [Visits],

          [TJ PPC B$].[Applications] AS [Applications],

          [TJ PPC B$].[New Applicants] AS [New Applicants],

          [TJ PPC B$].[New Email Addresses] AS [New Email Addresses],

          [TJ PPC B$].[Unique Visitors] AS [Unique Visitors],

          [TJ PPC B$].[Job Detail Views] AS [Job Detail Views],

          [TJ PPC B$].[CV Captures] AS [CV Captures],

          [TJ PPC B$].[Clicks] AS [Clicks],

          [TJ PPC B$].[New Visitors] AS [New Visitors],

          [TJ PPC B$].[Unique Applicants] AS [Unique Applicants],

          [TJ PPC B$].[Priority Applications] AS [Priority Applications],

          [TJ PPC B$].[Campaign] AS [Campaign],

          'Paid Search Brand' AS [Channel],

          'TotalJobs' AS [Board]

      FROM [TJ PPC B$]

       

       

      etc...

       

       

      This does require a lot more typing... But it worked.

       

      Until on expanding it to about half of the worksheets I am confronted with:querytoocomplex.png

       

      I am inclined to believe if I had managed to get the simpler query to work, it wouldn't be 'too complex' when expanded to all 100 sheets, so perhaps this would be the best approach? And if I could also add the 'Channel' and 'Board' in the Custom SQL as I have above, then that would be fantastic. If all fails, there are some sensible splits (by board for example) I could make, but then would have 6 workbooks rather than 1. I want 1.

       

      I attach a sample of the data I'm working with and am desperate for any suggestions you can offer!

       

      Many thanks,

       

      Ben

        • 1. Re: Custom SQL Union and Union All problems. Query too complex!
          Ben Eddings

          Jonathan Drummey, I gather you helped a colleague of mine on tuesday perfect her left join, which involved some Union Alls, any chance you know what's going on here?

           

          Ben

          • 2. Re: Custom SQL Union and Union All problems. Query too complex!
            Toby Erkson

            Ben Eddings wrote:

             

            ...

             

            In this file I have around 100 worksheets (6 boards multiplied by about 19 channels. This is necessary for the way ReportBuilder outputs) and need to Union each sheet, so in effect stacking them all on top of each other.

             

            The obvious way to join two sheets would be...

             

            SELECT * FROM ['TJ PPC$']

            UNION ALL

            SELECT * FROM ['TJ PPC B$']

            UNION ALL

            SELECT * FROM ['TJ PPC Camps$']


            ...but no matter what I tried, I couldn't make it work. Apparently The number of columns in the two selected tables or queries of a union query do not match. I'm not sure if blank columns on some sheets are the cause. All sheets have the same number of columns and matching column titles, but not necessarily in the same order or with data on every sheet.

            ...

            Ben, you gave the correct answer:

            The SQL UNION Operator

            The UNION operator is used to combine the result-set of two or more SELECT statements.

             

            Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.

             

            Correct your data and then try the simpler method.

            1 of 1 people found this helpful
            • 3. Re: Custom SQL Union and Union All problems. Query too complex!
              Ben Eddings

              I see, that explains why I couldn't Union for the sheet TJ PPC Camps with TJ PPC as the columns are in a different order. A relic of my ReportBuilder data connection's quirks. But what about between TJ PPC and TJ PPC B?


              SELECT * FROM ['TJ PPC$']

              UNION ALL

              SELECT * FROM ['TJ PPC B$']

               

              There the columns are in identical order and contain similar data types and yet the query I have written above still doesn't work. And it's baffling me. I've tried with and without apostrophes around the table name and various other slight alterations but it just refuses to play ball.

              • 4. Re: Custom SQL Union and Union All problems. Query too complex!
                Ben Eddings

                There's no error as such in that code, it's just too long! Those lines in effect create a new column in each table, the title of which is Channel, and then Paid Search or Paid Search Brand in every line.

                • 5. Re: Custom SQL Union and Union All problems. Query too complex!
                  Matt Lutton

                  I understand--like I said, that was the only noticeable difference in the coding for each table in the original post.  I am afraid I'm not much help--everything appears normal from what you've posted thus far.

                  • 6. Re: Custom SQL Union and Union All problems. Query too complex!
                    Russell Christopher

                    Hey Ben -

                     

                    You did nothing wrong here - your query is tight.

                     

                    I think your problem is the database engine that is attempting to execute it....(loved and equally loathed by many) JET.

                     

                    JET is used when you query Text, Excel and Access files. And, while it was great it in its time, its time has passed

                     

                    It just doesn't have enough juice to deal with what you're throwing at it, so it's giving up. When it gives up, we throw an error message, too.

                     

                    You can still get to where you want to go, but I think you're going to have to take this in smaller steps:

                     

                    • Figure out the maximum number of tables you can UNION together before Jet dies.
                    • Throw that data into a Tableau Extract
                    • Repeat this process with the rest of the tables. You may end up with 3-4 different extracts...maybe more.
                    • Use Tableau's ability to "Add Data From File" to push all the rows from each of your 1-many extracts into SINGLE extract. 

                     

                    This isn't going to be particularly pleasant experience, but I think it's your best bet considering JET won't be able to handle the "all up" query you're throwing at it.

                     

                    Good luck, hope this helps.

                    1 of 1 people found this helpful
                    • 7. Re: Custom SQL Union and Union All problems. Query too complex!
                      kettan

                      The error in the simpler query is that Tableau or Jet SQL doesn't like the empty column O in

                      sheet TJ PPC. After deleting column O and saving the file, this query worked:

                      (re-ordering columns in sheet TJ PPC Camps also needed to be done)

                       

                      SELECT * FROM ['TJ PPC$']

                      UNION ALL

                      SELECT * FROM ['TJ PPC B$']

                      UNION ALL

                      SELECT * FROM ['TJ PPC Camps$']

                       

                      Ps. It is normally best to use column names, but if that gives problems then better go for what works 

                      1 of 1 people found this helpful
                      • 8. Re: Re: Custom SQL Union and Union All problems. Query too complex!
                        kettan

                        Delete the nameless & empty Column O in TF PPC and this query should it work:

                         

                        SELECT * FROM ['TJ PPC$']

                        UNION ALL

                        SELECT * FROM ['TJ PPC B$']

                        • 9. Re: Custom SQL Union and Union All problems. Query too complex!
                          Fernando Furquim

                          Hello,

                           

                          it looks like Johan has resolved the two issues that would prevent this from executing. Just wanted to mention a couple of alternatives, such as using full outer joins or scalar subqueries in case your query runs slow. UNION is quite efficient though, so hopefully this solution works and runs quick.

                          1 of 1 people found this helpful
                          • 10. Re: Custom SQL Union and Union All problems. Query too complex!
                            Ben Eddings

                            Many thanks Russell Christopher and all others that contributed.

                             

                            Creating the extracts (6 of them) and then adding each one via 'Add Data from File...' certainly works. Up to a point. When I refreshed the Report Builder Connection into the Excel source, then in Tableau hit Data>Refresh All extracts, only the original extract around which the workbook was built (and to which the other extracts were 'Added from File' to) is refreshed. Data from the others is lost.

                             

                            I feel I'm missing your emphasis on ...

                             

                             

                            push all the rows from each of your 1-many extracts into SINGLE extract. 

                             

                             

                             

                            I'll explain exactly my steps and perhaps you can see where I went wrong.

                             

                            1. Create 6 extracts, i.e. pull the appropriate section from my vastly long query and use that section as the CustomSQL for each extract.
                            2. New workbook, data source being the first extract.
                            3. Extract>Add Data from File..., select next .tde file
                            4. repeat step 3. until all extracts are added.
                            5. PROFIT
                            6. Next day, refresh ReportBuilder data connections in excel and save.
                            7. In Tableau, Data>Refresh all Extracts, but only the original extract appears.

                             

                            refresh all extracts.png

                             

                                 8. On hitting Refresh, the new day's data is added beautifully, but only that in the original extract.

                                

                            It is certainly true I could refresh each extract and add them all again every day but it feels like there should be an easier way...

                             

                            What exactly do you mean 1-many extracts into a SINGLE extract?

                             

                            Thanks in advance,

                             

                            Ben

                            • 11. Re: Custom SQL Union and Union All problems. Query too complex!
                              Richard Leeke

                              Just came belatedly across this thread.

                               

                              I recall an old thread where someone hit the issue of the maximum number of UNION clauses in a JET SQL statement (maybe 50 or so) and got around it by nesting two levels of UNIONs. I can't recall the exact details, but I have the impression that he managed 100 or more chunks by dividing into sets of 10 or 20. Something vaguely like:

                               

                              SELECT *

                              FROM

                              ( SELECT * FROM A

                                UNION

                                SELECT * FROM B

                                UNION

                                SELECT * FROM C )

                              UNION

                              ( SELECT * FROM D

                                UNION

                                SELECT * FROM E

                                UNION

                                SELECT * FROM F )

                              UNION

                              ( SELECT * FROM G

                                UNION

                                SELECT * FROM H

                                UNION

                                SELECT * FROM I )

                               

                              I'll see if I can dig up the old thread.

                              • 12. Re: Custom SQL Union and Union All problems. Query too complex!
                                Ben Eddings

                                Well even without the old thread the example you wrote there works for me...

                                 

                                Interesting, tricking it into going under the 50 unions limit, I'll remember that!

                                 

                                Many thanks to all who offered suggestions, I must now be sure to pass it forwards.

                                • 13. Re: Re: Custom SQL Union and Union All problems. Query too complex!
                                  Richard Leeke

                                  I actually found the old thread and updated my post with a link to it right after posting that.  The forums seem to have eaten that.

                                   

                                  Here it is in case anyone is interested - and to give credit where it's due (@Stefan Berge) for coming up with the answer (even if I can't figure out how to make that @Mention link do anything).