Variable de procédure stockée & Power Query pour les moustachus

Le 22/05/2018 à 12:27

Bonjour à tous,

Me voici face à un problème épineux, dont je ne trouve aucune référence nulle part.

J'interroge une base Mysql via Power Query. J'y inclue une procédure stockée, pour que l'utilisateur interroge la base selon 2 critères le mois et l'année.

Ma requête fonctionne bien si je n'utilise pas la procédure stockée.

Voici comment je réalise ma requête Power Query.

let
//Définition des variables issues de ma feuille param tout est OK et vérifié
dbServeur = Excel.CurrentWorkbook(){[Name="param"]}[Content]{0}[Column1],
dbBase = Excel.CurrentWorkbook(){[Name="param"]}[Content]{1}[Column1],
dbAnnee = Excel.CurrentWorkbook(){[Name="param"]}[Content]{2}[Column1],
dbMois = Excel.CurrentWorkbook(){[Name="param"]}[Content]{3}[Column1],

//Création de la requête SQL
dbQuery =
"SELECT A.invoice_date ,G.name as Statut, A.id_order AS N_Commande, (case when H.transaction_id IS NULL then D.transaction_id else H.transaction_id end) AS id_transaction, A.total_paid AS Total_TTC, A.total_products_wt/1.2 AS TOTAL_PRODUIT_HT, A.total_products_wt AS Total_produit_TTC, A.total_shipping AS Frais_de_port_TTC,
 C.lastname AS Nom, C.firstname AS Prénom, C.email, F.phone, F.phone_mobile
FROM morders AS A
JOIN mcustomer AS C ON A.id_customer = C.id_customer
LEFT JOIN morder_payment AS D ON A.reference = D.order_reference
JOIN maddress AS F ON A.id_address_invoice = F.id_address
JOIN morder_state_lang AS G ON A.current_state = G.id_order_state
LEFT JOIN mpy_atos AS H ON A.id_cart = H.order_id
where G.id_lang = 2 
//le problème vient de la ligne ci-dessous
and YEAR(A.invoice_date) = '" &dbAnnee& "' AND MONTH(A.invoice_date) = '" &dbMois& "'
ORDER BY A.invoice_date",

//Application des données à la requête Power Query de ce côté tout fonctionne bien
    Source = MySQL.Database("monserveur.toto.fr", "malin", [ReturnSingleDatabase=true, Query=dbQuery])
in
    Source

Quand j'actualise ma requête Power Query j'ai le message d'erreur ci-dessous :

Désolé ... Nous n'avons pas pu appliquer l'opérateur & aux types Text et Number

Il semblerait que le problème vienne l'appel de mes variables '" &dbAnnee& "' et '" &dbMois& "' j'ai beau essayer tout un tas de syntaxes, je bloque, je galère ...

Le pire c'est qu'à un moment cela a marché pendant quelques heures. J'ai fait évoluer ma feuille Excel, et au final je me retrouvais avec le même message d'erreur.

Est ce que quelqu'un aurait une idée ? J'en peux plus ...

Le 22/05/2018 à 13:48

Salut Grouik

Et si tout simplement tu mettais des espaces ?

and YEAR(A.invoice_date) = '" & dbAnnee & "' AND MONTH(A.invoice_date) = '" & dbMois & "'

A+

Le 22/05/2018 à 13:54

Même chose avec les espaces.

J'ai le sentiment que c'est une histoire de format de valeur. J'ai essayé de mettre les cellules dont mes variables font références, en format "texte", "nombre" "standard", rien n'y fait ...

J'ai l'impression que c'est un truc dans le genre.

Merci en tout cas pour la proposition.

Le 22/05/2018 à 15:22

Bonjour à tous

Ces variables sont définies où ?

J'ai remarqué que PowerQuery ne veut pas concaténer avec & des nombres : peut-être définir en texte ou imbriquer dans un Number.ToText

Le 22/05/2018 à 15:38

Re,

Peut-être la solution ICI

A+

Le 22/05/2018 à 15:55

Pour 78chris

Ces 2 variables sont définies au début du code :

dbAnnee = Excel.CurrentWorkbook(){[Name="param"]}[Content]{2}[Column1],
dbMois = Excel.CurrentWorkbook(){[Name="param"]}[Content]{3}[Column1],

Concrètement, ce sont les cellules A3 Et A4 de ma feuille "param".

Je ne souhaite pas concaténer, j'ai cru comprendre qu'il fallait "encapsuler" la variable ainsi ' " & variable & " ' quand on déclare une variable dans Power Query et qu'on l'appelle dans la requête SQL. Mais à priori l'information est mauvaise, j'ai beau essayé d'autres syntaxe, cela ne fonctionne pas :

and YEAR(A.invoice_date) = '" & dbAnnee & "' AND MONTH(A.invoice_date) = '" & dbMois & "'

Peux tu m'aider un peu par rapport à tes propositions qui semblent intéressantes ? Je ne connais pas l'imbrication de Number.ToText, comment puis je l'utiliser ?

Qu'entends tu par définir en "text" ? J'ai déjà mis en format "Texte" les cellules concernées (A3 et A4).

Merci en tout cas pour tes pistes.

Pour BrunoM45 là aussi le lien renvoie vers des soucis de concaténation, ce qui n'est pas mon cas je veux juste appeler mes variables dans une requête SQL qui est dans Power Query.

Merci pour la piste proposée

Le 22/05/2018 à 16:20

Bonjour,

ta chaine est bien longue, et je trouve bizarre de voir des retours à la ligne sans la chaine " _". Sans parler de la virgule perdue toute seule à la fin.

Nonobstant ceci (j'aime bien ce mot ) et les interrogations sur les variables, il me semble qu'en plus il y a une limite quand on déclare une chaine dans le code.

On ne peut pas en écrire plus de x à la fois (255 ? une histoire de longueur de ligne de code vba).

Découpe un peu le travail :

Dim dbQuery As String
dbQuery = "SELECT A.invoice_date ,G.name as Statut, A.id_order AS N_Commande, "
dbQuery = dbQuery & "(case when H.transaction_id IS NULL then D.transaction_id else H.transaction_id end) AS id_transaction"
dbQuery = dbQuery & ", A.total_paid AS Total_TTC, A.total_products_wt/1.2 AS TOTAL_PRODUIT_HT, A.total_products_wt AS Total_produit_TTC,"
' etc

De toute façon ça ne fera pas de mal.

Et tu dois contrôler si la valeur de dbQuery est conforme à ce que tu attends pour pouvoir faire les corrections adéquates (mettre un Stop et fenêtre Espions ou variables locales).

eric

Le 22/05/2018 à 16:29

Merci Eriic pour ta proposition, je vais effectivement découper tout ça.

La virgule toute seule sépare ma variable dbQuery de la source.

Sinon si à la place des mes variables :

and YEAR(A.invoice_date) = '" &dbAnnee& "' AND MONTH(A.invoice_date) = '" &dbMois& "'

je tape directement le résultat de mes variables par exemple :

]and YEAR(A.invoice_date) = 2018 AND MONTH(A.invoice_date) = 2

et là, ça fonctionne. C'est vraiment juste la syntaxe de l'appel d'une variable dans une variable Power Query qui me manque, groumph ...

Le 22/05/2018 à 16:38

C'est vraiment juste la syntaxe de l'appel d'une variable dans une variable Power Query qui me manque

Là je laisse la main aux collègues....

dbAnnee est bien déclaré As Long ?

Parce que si [Content]{2}[Column1] te retourne un Range et que c'est ce que tu passes, il peut passer une propriété autre que .Value

Le 22/05/2018 à 16:45

RE

Quand on construit une requête sql en VBA on construit une chaîne et si des variables y figurent on les concatène avec le reste du texte de la requête.

Je suppose donc, sans certitude, que c'est pareil avec le langage M de PQ.

J'ai fait le test suivant

  • déclarer les deux variable en constantes : 2018 et 5
  • séparer les & pour faire une concaténation

J'ai le même message d'erreur que toi.

Je déclare "2018" et "5", je n'ai plus l'erreur

Je remet 2018 et 5 puis utilise

and YEAR(A.invoice_date) = '" & Number.ToText(dbAnnee) & "' AND MONTH(A.invoice_date) = '" & Number.ToText(dbMois) & "'

je n'ai pas l'erreur non plus

Number.ToText est une fonction proche de TEXTE en EXCEL ou FORMAT en VBA qui convertit un nombre en chaîne.

Dans la mesure où la requête est passée en tant que chaîne intégrant les variables, donc devenu des constantes une fois dans la chaîne, je ne vois pas pourquoi il faudrait une syntaxe particulière pour le nom de variable au moment où tu construis la chaîne...

Je ne peux pas tester plus avant n'ayant pas ta base...

Je pense qu PQ définit son typage indépendamment du type initial dans les cellules.

Il doit être possible de typer la déclaration de variable ce qui serait une piste supplémentaire ...

Le 22/05/2018 à 16:48

Bonjour

eriiic a écrit :

dbAnnee est bien déclaré As Long ?

Parce que si [Content]{2}[Column1] te retourne un Range et que c'est ce que tu passes, il peut passer une propriété autre que .Value

C'est du M pas du VBA, donc pas de _ avant les changements de ligne mais des , pour finir chaque instruction

pas de value ni range...

Le 22/05/2018 à 17:48

Ah si c'est du M alors...

Aussi je me demandais pourquoi le compilateur ne l'avait pas insulté avec une ligne comme ça.

eric

Le 22/05/2018 à 17:55

Bonjour le fil

C'est du M....

Mais quel album alors

Le 22/05/2018 à 17:59

Bon ben voilà c'est super les gars, ça fonctionne le ruse du Number.ToText, que je rebaptiserai la ruse de78chris ou le Teddy Riner de Power Query !!!

Avec ceci :

and YEAR(A.invoice_date) = '" & Number.ToText(dbAnnee) & "' AND MONTH(A.invoice_date) = '" & Number.ToText(dbMois) & "'

Merci à tous en particulier à 78chris qui a trouvé la solution. Un vrai moustachu !

Je sentais bien que c'est une histoire autour du format.

C'est top quand même l'entraide sur ce forum.

Un grand merci à tous

Le 22/05/2018 à 18:02

C'est un peu normal que PQ fasse de la M, non ?

Bon faut bien décompresser après avoir transpirer de la moustache encore merci

Le 23/05/2018 à 00:18

Rhoooo...

Rechercher des sujets similaires à "variable procedure stockee power query moustachus"