locked
Maximum Colums in a UNION statement RRS feed

  • Question

  • This Query won't work in PowerPivot For Excel....it maxes out the query somehow.  If I remove 86 it works...Wierdly enough 85*3 is 255...But I can have 10 Unions with 85 in them each and it works.   This is killing me.  Any help?

    I am getting multiple reports and consolidating them in one dashboard.  The reason I need so many columns is I am using the slicers in Excel 2010 and unfortunately there is a poor design where the slicers can only work on ONE table, so IF want my users to pick one practice, they would have to pick it multiple times for each table, so to get around this, I put all my data in one long table so I only need one slicer.

    you can easily replace the path with a dummy XLS file.

    select 1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    81,82,83,84,85,86
       from `C:\UHS-KPI\UH_PB_KPI_AR_Aging_by_Original_Service_Date.xlsx`.[UH_PB_KPI_AR_Aging_by_Original_$]


    UNION ALL

    select 1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    81,82,83,84,85,86
       from `C:\UHS-KPI\UH_PB_KPI_AR_Aging_by_Original_Service_Date.xlsx`.[UH_PB_KPI_AR_Aging_by_Original_$]

    UNION ALL

    select 1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    1,2,3,4,5,6,7,8,9,10,
    81,82,83,84,85,86
       from `C:\UHS-KPI\UH_PB_KPI_AR_Aging_by_Original_Service_Date.xlsx`.[UH_PB_KPI_AR_Aging_by_Original_$]

     


    me

    Wednesday, January 16, 2013 4:40 PM

Answers