using WITH <name> AS (...) and ADO 2.8

[goaway1234]

I am not an oracle expert, so this could be simpler than I'm making it out to be. I am working on an asp site that uses an Oracle back-end. On a particular page I have something like:

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "..."
cnn.Open
...
rst.CursorLocation = adUseClient
rst.Open strSQL, cnn, adOpenStatic

strSQL is a SELECT statement using a named sub-query (WITH ... As) The rst.Open statement executes without error, but the recordset does not open. Trying to get data from the recordset or examine its EOF property causes an 'object is closed or invalid...' error and rst.State indicates that the recordset is closed. The same sql statement works fine when I run it in SQLPlus.

Has anybody seen this before, and know how to work around it? If not, can you tell me of a way to use a field alias in the WHERE clause without the named sub-query?

The application that I am working on is old, very inflexible, and wrought with red-tape. I cannot do the more obvious solutions such as creating views, renaming fields, or changing anything in the database without breaking existing code and going through a frustrating proposal/approval process. Any help would be greatly appreciated.

 

[haste]

I can't say I have worked in such a situation but I'd like to help.
What the syntax of SELECT stmt in the strSQL variable I'm not familiar with it. If possible give the sql itself.

 

[goaway1234]

Thanks for the reply. I have found another way to do what I needed, though. The SQL that wouldn't open was

WITH Stations AS (SELECT contents as station, type as sector, type2 as region, side as address, side2 as city, side3 as state, side4 as zip FROM dropdown WHERE category='Station') SELECT stations.region, stations.sector, job_categories.* FROM nwd.stations, nwd.job_categories WHERE job_categories.station (+) = stations.station ORDER BY stations.region, stations.sector

This statement works fine and returns exactly what I expect in SQLPlus or PLSQL Developer, but when I try to open an ADO recordset from it on an asp page, the wierdness starts.