Pass through Query to Oracle Problems

[Bonediggler1]

Hello -

I have a somewhat complex Oracle query (complex because lots of nested case statements) that runs fine out of Oracle. However, when trying to get this to work in an Access Pass Through, it throws the error "Pass through query with ReturnsRecords property set to true did not return any records".

I am guessing this is because the query is too complex.

In initial development of this query I was using less case statments but a few subqueries. This also threw the same error. However after removing the subqueries it ran fine...this is what leads me to beleive access can only handle so much complexity with a pass through.

Any ideas??

Thank you!!

 

[sxschech]

Are you running the pass through query such as "Select * From Oracle" or are you actually running the sql statement itself within Access? "Select a, b, c
"From Oracle
"Where case when..."

If the second, you could try creating a view on the oracle side and then simply do the select *, but if you already are doing that, then you may either need to find another way to simplify the sql or create a temporary table on the oracle side that you can then easily query off of.

 

[Bonediggler1]

That is the frustrating part; I can neither get rights to create views nor create temp tables (or anything else for that matter besides running SELECT queries) in Oracle. We have read only rights to a reporting snapshot of the production database.

Nevertheless, are you confirming that complex queries cannot be used in a pass-through to Oracle? Any idea what the specific limitations are?

 

[sxschech]

Perhaps we can look at your sql statements. Otherwise, is there the possibility of creating some simpler pass through statements to oracle from access. Then you can do your joins or other criteria with standard Access queries based on the pass thrus. Another alternative is to create the temp tables in access and do your standard access queries off of the local temp data. Although the last approach may end up bringing down more data than you need.