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 COUNT(*) as groupCount FROM (SELECT name FROM table FOR READ ONLY ) AS DS (name)
This really don't work? why?
asked 12 Mar '13, 09:41
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 )