-
Notifications
You must be signed in to change notification settings - Fork 15
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
SQLSelect -->iSQLQuery_ToString - Problem with Join on 2 or more Tables #32
Comments
Can you please supply a code snippet, what the resulting SQL is, and what you expected? Even better would be if you were able to provide a failing test case that I could add to the unit tests. The test would, by definition, include the generation code as well as the expectation. The tests already include this:
the second of which includes two inner joins. |
Rereading your comment, are you calling |
It looks like I think I’ll work on modifying addTable to be called multiple times, however, this is not a join, and might not be what you are referring to. |
#33 may be related to this, and it has been fixed. Please let me know how to reproduce this issue or if that fix resolved your issue. |
Sorry for the late reply. To give you a proper example, suppose u have 4 Tables like the example below: The query I am trying to build is to get data from "tblDataRecords", but JOIN with the other three to get the respective ClassName. Query i need to Create:
Code Snipet:
Resulting Querys:
The problem only occurs if i attempt to JOIN 3 or more Tables, check code Snipet:
Resulting Query:
When i attempt to run this query it returns Error |
What is the error that you are seeing? Does
When I run your first code snippet, I get:
However, both code snippets look to be the same. Can you update your library code from the repository if you have not recently done so? |
Three years ago I changed AddJoin to allow multiple joins. It used to be:
and it is now:
Does your version of the code have the older version of the function? |
Just edited my previous post. I don't have a older version. I downloaded a couple of days before creating this issue. here is the function i asked for: To answer your question:
Query that i expected and that runs great on Access:
Query that is returned by iSQLQuery_ToString:
The JOINS are not correct. Compare the 2 Querys. |
Is this line correct?
should it be
|
OK .. i edited the posts once more .. and corrected it again.
when i try to run this on Access i get the following error: On the other hand, the query i gived u as example:
If necessary i can provide u with the Access Data Base itself and a Excel Macro Workbook with the code for u to test yourself. |
I don’t see where a problem would be. The parenthesis are optional, so that shouldn’t cause a problem. Can you try using table aliases instead of leaving them as empty strings? I’d have to look at the code, but maybe it’s adding an extra space between the table name and “ON”. Google says that error usually is from non-ascii table names or having spaces in field names, but that’s not the issue here. is the final semicolon a must have? |
Ahh! In MS Access, parenthesis are not optional. I think the fix will be: if number of joins is greater than two, for all joins except the last one add an open-paren after the join type, and a closing-paren before the ON. |
The final semicolon is only required in the MS Access query editor if i am not mistaken |
when u get a fix for this, let me know, so i can make a test and give you feedback about it. |
Not sure if what u say will work.. I give you a sugestion to work with and i'm pretty sure that it will require minimal code changes. Current Output of SQLSelect.iSQLQuery_ToString:
Correct Query that works on MS Access:
|
Thanks! If you’ve made a change to the code in the library and want to submit a pull request, go ahead. |
Think this should do the trick. File: SQLSelect.cls
|
As you suggested, created a pull request with a modified Function JoinString() I made a couple of tests and all of them worked on MS Access. |
Issue detected when attempting to create SQLString if 2 or more tables are necessary to JOIN.
the resulting SQL contains error and cannot be used.
The text was updated successfully, but these errors were encountered: