I think the problem is with your logic, not the query. Doing this:
<cfquery name="x" datasource="##" username="##" password="##">
WITH myResults AS
SELECT 1 AS myLevel, 'someString' AS myData
Shows the results as expected.
thanks but it does the same thing, returns:
Variable X is undefined.
37 : </cfquery>
39 : <cfdump var="#x#"><cfabort>
i looked at the oracle driver on the cf machine, and it's version 8 (the db is 10g). i'll upgrade the driver and see if it works
If you read the error message, you'll realise none of this is down to Oracle and by no means is that error message "the same thing". Did you add the name="x" attribute to the query? You can't just run half the code and expect it to work.
yes, the query always had the name attribute set
it was definitely the oracle driver, i've install v10.1 in parallele with 8.1, recreated my datasources and everything works fine now
thanks for the reply