INNER JOIN via ODBC

Business Central/NAV Developers Forum
1

Hi! I am trying to create joined queries via asp->odbc->navision. I’ve tried to use a very basic query in order to solve the problem but I am still getting the same error message: The query is: Select TABLEx.FIELDa, TABLEy.FIELDb from (TABLEx inner join TABLEy on TABLEy.FIELDb = TABLEx.FIELDa) Note, I’ve tried the query with and without parenthesis. The error message is: SQL error: [Navision Software a/s][Navision Financials ODBC Driver]Expected lexical element not found: . I’ve tried this query in Access with linked tables and it works fine. Does somebody know any solution to this problem? Best regards, Andreas Åsander Clavister AB Edited by - Andreas_Enternet on 2001 Dec 11 14:25:15

2

Hi Andreas I don’t think it is possible to make these joins in the Navision ODBC-driver. You can import Your tables in to MS Access and make the Join there. Best Regards Mads Morre DK

3

Hi! Ok, that sure gives me some headeache :frowning: This application needs to be executed in run time in order to fill it’s purpose. Thanks anyway! BR, Andreas Åsander Clavister

4

Hi How about using linked tables in Access linked to the Naviosn database through odbc. Then do your joins in access or even better use the SQL option of Navision. The Main problem is that Navisions ODBC driver can be very slow and sometimes returns all the values in the table for the host to sort out which are required. The driver also times out if you don’t up the time out value, you also have to create keys for the data you require by trial and error. The order of the keys seems to be important for ODBC as it uses the first one which is a near match even if there is a better one later. Paul Baxter

5

If you are able to modify the query easily (i.e it is not dynamically generated by another layer such as ADO), you can express it with the non-JOIN joining syntax: SELECT X.FIELDa, Y.FIELDb FROM TABLEx X, TABLEy Y WHERE on X.FIELDa = Y.FIELDb

6

Hi Robert! You are a life saver :slight_smile: It worked out just fine, a bit slow though, but anyway. That’s just for me to optimize now. Thanks a lot!!! Best regards Andreas Åsander