none
Longueur d'une instruction SQL SELECT UNION dans le QueryEditor de PowerPivot RRS feed

  • Question

  • Bonjour,

    j'essaie d'écrire et exécuter une requête PowerPivot via le QueryEditor. J'ai besoin de fusionner (merge) plusieurs tables identiques de plusieurs fichiers. J'ai donc créé une requête SQL UNION qui va bien.

    Mon problème est que celle-ci fait 260 lignes et PowerPivot n'aime pas.

    J'ai le message d'erreur suivant :  "ERROR [HY000] : Microsoft Excel ODBC - requête trop complexe".

    Si je réduis le nombre d'instructions SELECT UNION à une centaine de lignes (100 aprox.), c'est bon, ça passe.

    Quelqu'un aurait une idée ? Est-ce une limite physique qu'on ne peut dépasser et je dois trouver un autre moyen pour mon "merge" ou existe-t-il un paramètre ou autre pour permettre d'écrire une longue query SELECT UNION.

    Ou une autre idée ? Je suis preneur.

    Merci beaucoup pour le coup de mains. A bientôt.

    Jean

    jeudi 30 juillet 2015 13:26

Toutes les réponses

  • Peut on avoir un extrait (léger) pour voir à quoi ressemble votre requete afin d'estimer comment limiter le nombre de ligne.
    jeudi 30 juillet 2015 14:13
  • Oui, bien sûr.

    En fait, j'ouvre une table et je fais plusieurs SELECT UNION pour "remettre à plat" les données (j'essaie de faire en qqsorte ce que fait la fonction UNPIVOT de PowerQuery, car malheureusement je n'ai pas PowerQuery).

    Et ensuite je fais ce même groupe de SELECT UNION pour chacune de mes tables à merger.

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

    Bout du code .... :

    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI50-INT]),0,[DI50-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI50-CDS]),0,[DI50-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI50-FFT]),0,[DI50-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI51-INT]),0,[DI51-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI51-CDS]),0,[DI51-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI51-FFT]),0,[DI51-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI52-INT]),0,[DI52-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI52-CDS]),0,[DI52-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI52-FFT]),0,[DI52-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI53-INT]),0,[DI53-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI53-CDS]),0,[DI53-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI53-FFT]),0,[DI53-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI54-INT]),0,[DI54-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI54-CDS]),0,[DI54-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI54-FFT]),0,[DI54-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI55-INT]),0,[DI55-INT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI55-CDS]),0,[DI55-CDS]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI55-FFT]),0,[DI55-FFT]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI2' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI2]),0,[DI2]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI3' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI3]),0,[DI3]) As Units FROM [PRJ-DI50$]
    UNION ALL
    SELECT 'DI50' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI4' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI4]),0,[DI4]) As Units FROM [PRJ-DI50$]

    UNION ALL

    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI50-INT]),0,[DI50-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI50-CDS]),0,[DI50-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI50-FFT]),0,[DI50-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI51-INT]),0,[DI51-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI51-CDS]),0,[DI51-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI51' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI51-FFT]),0,[DI51-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI52-INT]),0,[DI52-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI52-CDS]),0,[DI52-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI52' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI52-FFT]),0,[DI52-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI53-INT]),0,[DI53-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI53-CDS]),0,[DI53-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI53' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI53-FFT]),0,[DI53-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI54-INT]),0,[DI54-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI54-CDS]),0,[DI54-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI54' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI54-FFT]),0,[DI54-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI55-INT]),0,[DI55-INT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI55-CDS]),0,[DI55-CDS]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI55' as RessOwner, 'FFT' as RessType, IIF(ISNULL([DI55-FFT]),0,[DI55-FFT]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI2' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI2]),0,[DI2]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI3' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI3]),0,[DI3]) As Units FROM [PRJ-DI51$]
    UNION ALL
    SELECT 'DI51' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI4' as RessOwner, 'UO' as RessType, IIF(ISNULL([DI4]),0,[DI4]) As Units FROM [PRJ-DI51$]

    UNION ALL

    SELECT 'DI52' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'INT' as RessType, IIF(ISNULL([DI50-INT]),0,[DI50-INT]) As Units FROM [PRJ-DI52$]
    UNION ALL
    SELECT 'DI52' As ActOwner, [Activités], [Projet OGPP], [Type], [Code ABC], [Owner], 'DI50' as RessOwner, 'CDS' as RessType, IIF(ISNULL([DI50-CDS]),0,[DI50-CDS]) As Units FROM [PRJ-DI52$]
    UNION ALL

    jeudi 30 juillet 2015 15:02
  • essayer de créer des vues pour éviter de présenter cette requete directement.
    jeudi 30 juillet 2015 15:08
  • Le souci, c'est que ce ne sont que des fichiers plats. Je ne peux pas créer de vues, les tables en question ne sont pas dans une BDD. Ce sont des tables au format plat et j'y accède par ODBC.

    Je veux bien en effet créer une vue ou une sous-requête, mais je vois pas comment je peux le faire avec juste Excel et PowerPivot.

    jeudi 30 juillet 2015 15:28
  • pour ceux que cela pourrai intéresser :

    Solution 1 : bah j'ai écrit un script VB qui fait ce que ferait un UNPIVOT ... je voulais éviter cela, mais au final, le code n'est pas compliqué et donc ça transforme mes fichiers au bon format

    Solution 2 : soufflée par un user du forum dans sa version US, j'ai installé l'add-in gratuit et complémentaire à PowerPivot = Power Query .. c'est pas mal, c'est même top et plus facile. Le truc c'est qu'avec le script VB, n'importe qui peut le faire tourner pour obtenir les fichiers au bon format pour les charges dans PwPivot ... 

    voilà ... a+

    • Proposé comme réponse PRODWARE-CLOUD mercredi 5 août 2015 14:58
    • Non proposé comme réponse PRODWARE-CLOUD mercredi 5 août 2015 14:58
    • Proposé comme réponse Grégory_Nail mercredi 5 août 2015 14:58
    mercredi 5 août 2015 13:48