ODBC et les sous-requêtes

[sgarneau]

J'ai une requête SQL qui marche très bien dans MS Access...maintenant j'essaie de la faire fonctionner directement dans le code source où encore dans openoffice et je ne réussi pas à obtenir de résultats.

Questions: Quelqu'un connait la syntaxe équivalente ou encore savez-vous comment peut-on voir ce que MS Access envoie comme command à l'ODBC? (j'ai essayé de tracer mais sans succès)

Requête access:

SELECT InCustomerSupplierNumber, sum(invoice_30.t3045) as t3045, sum(invoice_45.t4560) as t4560, sum(invoice_60.t6090) as t6090, sum(invoice_90.t90120) as t90120, sum(invoice_120.t120plus) as t120plus FROM (
SELECT InInvoiceNumber, InReference, InDescription, InvoiceAR.InCustomerSupplierNumber, invoice_30.t3045, invoice_45.t4560, invoice_60.t6090, invoice_90.t90120, invoice_120.t120plus
FROM ((((InvoiceAR
LEFT JOIN (SELECT InvoiceAR.InInvoiceTotal AS t3045, InvoiceAR.RecCardPos FROM InvoiceAR WHERE InvoiceAR.InAllPaid=0 AND InvoiceAR.InDate Between #08/15/2010# And #10/30/2010#) AS invoice_30 ON invoice_30.RecCardPos = InvoiceAR.RecCardPos)
LEFT JOIN (SELECT InvoiceAR.InInvoiceTotal AS t4560, InvoiceAR.RecCardPos FROM InvoiceAR WHERE InvoiceAR.InAllPaid=0 AND InvoiceAR.InDate Between #6/30/2010# And #08/14/2010#) AS invoice_45 ON invoice_45.RecCardPos = InvoiceAR.RecCardPos)
LEFT JOIN (SELECT InvoiceAR.InInvoiceTotal AS t6090, InvoiceAR.RecCardPos FROM InvoiceAR WHERE InvoiceAR.InAllPaid=0 AND InvoiceAR.InDate Between #04/15/2010# And #6/29/2010#) AS invoice_60 ON invoice_60.RecCardPos = InvoiceAR.RecCardPos)
LEFT JOIN (SELECT InvoiceAR.InInvoiceTotal AS t90120, InvoiceAR.RecCardPos FROM InvoiceAR WHERE InvoiceAR.InAllPaid=0 AND InvoiceAR.InDate Between #02/28/2010# And #04/14/2010#) AS invoice_90 ON invoice_90.RecCardPos = InvoiceAR.RecCardPos)
LEFT JOIN (SELECT InvoiceAR.InInvoiceTotal AS t120plus, InvoiceAR.RecCardPos FROM InvoiceAR WHERE InvoiceAR.InAllPaid=0 AND InvoiceAR.InDate Between #11/30/2009# And #02/27/2010#) AS invoice_120 ON invoice_120.RecCardPos = InvoiceAR.RecCardPos
WHERE InvoiceAR.InAllPaid=0
AND InvoiceAR.InDate Between #11/30/2009# And #11/30/2010#
AND (t3045 is not null OR t4560 is not null OR t6090 is not null OR t90120 is not null OR t120plus is not null)
) a
GROUP By InCustomerSupplierNumber
ORDER By InCustomerSupplierNumber

[Jeremie]

Bonjour,

Je n'ai pas encore pris le temps d'analysé votre requête, mais, il me semble que votre format de date ne sois pas le bon.

Code:


InvoiceAR.InDate Between #11/30/2009# And #02/27/2010#)

Devrait être dans votre code :

Code:

InvoiceAR.InDate Between {d '11/30/2009'} And {d '02/27/2010'})

Et il me semble aussi qu'Acomba est des problèmes avec le Between, mais je ne me souvient plus pu j'ai lu ça.

Il serait bien aussi que vous donniez le message d'erreurs que vous avez lorsque vous exécuté votre requête via votre code.

bonne journée.

[sgarneau]

Oui effectivement j'ai constaté le problème avec le between, c'est drôlement plus lent.

Au fond ce que tente de solutionner, c'est de faire une jointure sur une sous-requête. Avec MSAccess et un lien ODBC c'est possible, cependant je ne réussit pas a trouver la façon de faire.

Ex: SELECT table1.field1 FROM table1 LEFT JOIN (SELECT field1 FROM table1 WHERE condition = x) subselect1 ON subselect1.field1 = table1.field1


Le message d'erreur que je reçois:
Statut SQL: 42000
Code d'erreur: 1015
[Acomba ODBC Driver]Expected lexical not found:

[Contenu sponsorisé]