1

I tried to run this in TOAD and sqldeveloper is ok, but when I run in Access with passthrough query I got an error: "pass-through query with returnsRecords property"

hereby the code

With T1 as (select * from baasa), 
T2 as (select * from lalala), 
T4 as  (select * from babab)
select distinct T1.C11 as something, T4.C5 as somewhere, T2.C2 as what
from T1,T2,T4
Where T1.C11=T4.C5 and T1.C10=T2.C2

or in fiddle: http://sqlfiddle.com/#!4/84c06/2 can anyone help me?

Improve this question
5
  • I'll admit that I haven't worked with Oracle for a long time, but I don't think you've given us enough of your query to identify the problem. I know for certain that Access pass-through queries can use Common Table Expressions on SQL Server, so I don't see why you shouldn't be able to use them on Oracle too. Just to confirm: Your query does return records, and the Returns Records property of the pass-through query is set to Yes, correct? Commented Nov 28, 2013 at 15:49
  • it returned records when i use sqldeveloper but when i used access i got this erro: "pass-through query with returnsRecords property". may i know how to set it to yes or no? thank Commented Nov 28, 2013 at 17:37
  • 1
    Your question as stated makes no sense. Your SQL fails with "ORA-00928: missing SELECT keyword". Check the SQL Fiddle here to verify. Also, "pass-through query with returnsRecords property" is not a valid Access error message. We cannot help you if you do not tell us what you are really doing. Commented Nov 28, 2013 at 18:41
  • @GordThompson i added there sqlfiddle.com/#!4/84c06/2 , im sorry for confusion. Commented Nov 28, 2013 at 19:20
  • @a_horse_with_no_name its TOAD lol.. Commented Nov 28, 2013 at 19:22

2 Answers 2

Reset to default
3

Your (amended) query works fine in SQL Server 2008 R2, and it also works fine as an Access pass-through query to same. Verify that the Returns Records property of the query is set to Yes (as seen below) and try running the pass-through query again. As I said in my initial comment to your question, if it works for SQL Server then I see no reason why it shouldn't work for Oracle.

ptq.png

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@Xarxas Did you click the "Pass-Through" button in the "Query Type" section of the ribbon bar? (Take another look at the screenshot, above. It's the button highlighted in yellow.)
in ODBC connection Str what should i fill in ( im sorry for lots of stupid questions)
@Xarxas Take a look at the Microsoft article here.
and what strange is also, when i tried to import the tables into my local database it works fine. but when i tried to connect via ODBC to oracle it didnt work... :(
it works now:) i changed it, removed the "with" so the code is : select * from (select * from table1) where something. thanks!
1

I'm pretty sure your query has a syntax error: there's no SELECT nor FROM clause before the WHERE clause...

Improve this answer

1 Comment

im sorry i added there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.