FOR READ ONLY on subquery

[Former Member]

I try to found some information about limitations of used a SELECT using FOR READ ONLY on subqueries (Ultralite), everytime i try this an error occurs:

Syntax error near 'FOR' [SQL Offset: 1735] SQLCODE=-131, ODB 3 STATE="4200"

Select example:

SELECT COUNT(*) as groupCount FROM (SELECT name FROM table FOR READ ONLY ) AS DS (name)

This really don't work? why?

[Breck_Carter · Accepted solution]

Short answer: Subqueries can't be anything other than read-only, so FOR READ ONLY is superfluous.

The FOR READ ONLY clause is only allowed in an outer SELECT statement, not a subquery, because only a SELECT statement can be used to fetch and update rows in a cursor... in other words, FOR UPDATE and hence FOR READ ONLY make no sense on a subquery... so you'll get the same SQLCODE -131 if you run your query on a SQL Anywhere server.

Here are a couple of examples...

Could not execute statement.
Syntax error near 'READ' on line 5
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1

SELECT *
  FROM dummy
 WHERE dummy_col IN ( SELECT dummy_col
                        FROM dummy
                      FOR READ ONLY )

-----

Could not execute statement.
Syntax error near 'UPDATE' on line 5
SQLCODE=-131, ODBC 3 State="42000"
Line 1, column 1

SELECT *
  FROM dummy
 WHERE dummy_col IN ( SELECT dummy_col
                        FROM dummy
                      FOR UPDATE )