I have an order system. Users with can be attached to different orders as a type of different user. They can download documents associated with an order. Documents are only given to certain types of users on the order. I'm having trouble writing the query to check a user's permission to view a document and select the info about the document.
I have the following tables and (applicable) fields:
I have the following query:
The trouble is that in the Exists Select, it does not recognize Docs (at Docs.FileNo) as a valid table. If I move the second on argument (
Why doesn't this work?
In ADS 10.
asked 15 Jan, 10:32
This is indeed an erroneous limitation in the query engine. The Join Condition can only contain columns from table in the join group or constants. In this construct, although the Docs.FileNo is a table column, it should be treated as a constants because its value is from the parent query and is constant during each execution of the child query.
I will log this in our bug tracker and have it fixed in the next update release.
Thank you for reporting the bug. I do not know a workaround except the one you already mentioned.
answered 15 Jan, 13:46
Within the LEFT JOIN definition you can only use those tables in the ON condition that are part of the JOIN. Having not too much other experiance with other SQL database I would still expect this to be the case elsewhere as well.
I do not believ that doing an EXISTS() on a OUTER join is a good idea here as the OUTER join (a LEFT join is an outer join) will find a row even if there is no matching record in FileUsers. As the only WHERE close inside the EXISTS() part is based on the left table DocAccess, the EXISTS statement probably returns TRUE, as soon as there is a DocAccess row with UserNo 2000, even if it does not match the ON condition. Do you get what I mean?
should do the trick I think.