Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB Connectivity » View Thread |
Messages 1 to 8 of 8 total |
MS Query and joins |
Mon, Sep 24 2018 10:51 AM | Permanent Link |
Charles Bainbridge | We have customers testing using EDB ODBC various Excel-based queries they originally created against our older DBISAM-based product. Inner joins are a problem - see SQL generated by MS Query below.
SELECT Orderh.REF, Orderh.COMPANY, ORDERI.LINE, ORDERI.PRODUCT, ORDERI.DESC1, ORDERI.QUANTITY FROM "_DEFAULT".Orderh Orderh, "_DEFAULT".ORDERI ORDERI WHERE Orderh.REF = ORDERI.REF This runs at a truly glacial speed as EDB does not optimize SQL-89 style joins - from the EDB manual:- << ElevateDB does not optimize join expressions in the WHERE clause, otherwise known as SQL-89 style joins. You must use the JOIN clause in order to have ElevateDB optimize the joins. >> Is there any way to work around this to get some level of performance, short of the users having to edit their SQL directly? Are there any alternatives to MS Query for use with EDB and Excel? |
Mon, Sep 24 2018 1:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Charles,
<< Is there any way to work around this to get some level of performance, short of the users having to edit their SQL directly? Are there any alternatives to MS Query for use with EDB and Excel? >> Unfortunately, there's really no way around this other than to edit the SQL directly. MS Query is just really, really old and hasn't been updated in years. Tim Young Elevate Software www.elevatesoft.com |
Tue, Sep 25 2018 2:39 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Charles
If most of the queries are similar to the one posted it wouldn't be overly difficult to interpose your own translator to give the more modern syntax. Roy Lambert |
Wed, Sep 26 2018 6:45 AM | Permanent Link |
Adam Brett Orixa Systems | Charles,
It is not too hard to substitute custom SQL in Excel, rather than use the default generated by MS Query, which is absolutely awful. I usually start from the "Data" tab of excel, click on "Connections" and click "Add", from here you can create a connection to an EDB DB (provided the ODBC is installed), and fill in the SQL in the Connection Properties. It is fiddly, and not obvious, but it all works and allows you to write the best SQL. I do have a word document explaining how to do it in steps for my users if you want this I will post it to the NGs. |
Wed, Sep 26 2018 6:59 AM | Permanent Link |
Charles Bainbridge | Adam Brett wrote:
It is fiddly, and not obvious, but it all works and allows you to write the best SQL. I do have a word document explaining how to do it in steps for my users if you want this I will post it to the NGs. Hi Adam, That would be useful, thanks. Though I suspect the people using MS Query like it as they don't have to roll their own SQL. |
Wed, Sep 26 2018 11:14 AM | Permanent Link |
Adam Brett Orixa Systems | Charles,
My documentation skills leave a lot to be desired. Feel free to use the attached Word Document as you wish. It is not particularly well written or clear, but does show how to set up an Excel sheet and then link it to your own SQL. The main point is that once a "Data Connection" is created, it is fairly easy to paste your own SQL into the Data Connection >> Definition >> Command Text. You definitely need skills to write the SQL, you are right that customers may not have these. I usually provide the base SQL for my customers, and then give them tips and training on extending it. Attachments: Accessing Database using SQL from Excel.docx |
Thu, Sep 27 2018 11:45 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Charles,
<< Though I suspect the people using MS Query like it as they don't have to roll their own SQL. >> I'm looking into the new Power Query functionality in Excel to see if it will work as a substitute. The only problem is that it looks like there's some issues with it and the EDB ODBC Driver, so I need to iron those out and then it should be a good substitute. Tim Young Elevate Software www.elevatesoft.com |
Fri, Sep 28 2018 5:59 AM | Permanent Link |
Charles Bainbridge | Tim Young [Elevate Software] wrote:
I'm looking into the new Power Query functionality in Excel to see if it will work as a substitute. The only problem is that it looks like there's some issues with it and the EDB ODBC Driver, so I need to iron those out and then it should be a good substitute. Much appreciated Tim. We've also been looking Power Query too. |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |