Rather than use a formal notation to present the SQL syntax used in SQL Anywhere, the Help has always used an informal, flexible "user-friendly" notation.
Sadly, with informality comes imprecision, which leads to confusion, in one case involving the syntax for "expression":
ExpressionsThe problem is, SQL Anywhere implements more than one form of expression depending on the context, and the Help only describes one form. In particular, the definition of "expression" includes "( subquery )", and the syntax for the CALL statement allows for expressions as parameters, leading one to believe that subqueries can be passed as CALL parameters:
An expression is a statement that can be evaluated to return values.expression: case-expression | constant | [correlation-name.]column-name | - expression | expression operator expression | ( expression ) | function-name ( expression, ... ) | if-expression | special value | ( subquery ) | variable-name | sequence-expression
CALL statementUnfortunately, in SQL Anywhere 12 and earlier, such was not the case:
Invokes a procedure.[variable = ] CALL procedure-name ( [ expression, ... ] )
The good news, SQL Anywhere 16 has caught up with the Help in this respect; subqueries are now allowed in CALL statements:CALL sa_db_info ( ( SELECT 0 FROM DUMMY ) ); Could not execute statement. Syntax error near ')' on line 1 SQLCODE=-131, ODBC 3 State="42000" Line 1, column 1
CALL sa_db_info ( ( SELECT 0 FROM DUMMY ) ); Number,Alias,File,ConnCount,PageSize,LogName 0,'ddd16','C:\\projects\\$SA_templates\\ddd16.db',2,4096,'C:\\projects\\$SA_templates\\ddd16.log'
1 comment:
I have wanted to use subqueries in CALL statements MANY times. And I missed this point in the new features of SQLA 16. Excellent to know!
Post a Comment