factored subquery with clause

[Sean_UK]

Hi,

I have been writing queries that contain a factored subquery, like:

WITH My_Table AS
(
SELECT *
FROM Table_Name
)
SELECT * FROM My_Table

This works when using SQL+, but not when using PL/SQL developer. Does anybody know if this is a limitation of the software, is it a setting within the software, or a setting somewhere else? It isn't that it doesn't work, the query runs, but no records are returned. When I look at the Explain Plan it looks the same in PL/SQL Developer as it does in SQL+, same Bytes, Cardinality etc etc.

I've also tried using such queries from VB, again returning empty recordsets (in fact, a recordset object does not become created).

Any thoughts on this I'd be most interested!

Cheers,

Sean.

 

[jlcox]

This syntax works as expected for me with v 7.1.5, i.e., I get query results just as I should.

 

[Sean_UK]

Thank you, that's a good start. So at least now I know it's something in my set up, rather than a software limitation.

I'm using 7.0.3.1123, but I don't imagine the version difference would be that significant.

I don't suppose you have any idea whether this would be due to settings within the PL/SQL Developer or within the database?

When I send the query as a string command from VB/VBA I also get the empty set problem. It's as if the temp table returns no records, but I can see it is there in the Explain Plan.

Thanks again, that's a step forward for me at least!

Sean.

 

[Sean_UK]

OK, solved!

My Oracle Home was pointing to the Default Home, which was using an Oracle install of version 8. So PL/SQL was happy with the query, the database (v10) was happy and returned data, but somewhere along the line the data wasn't getting shown by PL/SQL developer. I guess the version 8 client didn't know how to deal with it? I don't know exactly how this works, as you can tell. But, anyway, by using the Oralce Client 10g as my Oracle Home, not only does the query run, but the results are displayed too.

Now I need to address the VB problem...

Thanks for the help. The fact that your query was working spurred me on to keep trying. Got there eventually.

Cheers, Sean.

 

[Sean_UK]

Solved for VBA (and presumably something similar for VB too).

For anyone that might have the same problem, the reason my VBA code was not returning the data was because I was using a Microsoft providor instead of the Oracle one (MSAORA.1 instead of OraOLEDB.Oracle.1). Switching provider then enables me to retrieve all the data as I'd hoped.