Tuesday, April 2, 2013

It's just an expression

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":

Expressions

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
The 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:
CALL statement

Invokes a procedure.
[variable = ] CALL procedure-name ( [ expression, ... ] )
Unfortunately, in SQL Anywhere 12 and earlier, such was not the case:
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
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 ) );

Number,Alias,File,ConnCount,PageSize,LogName
0,'ddd16','C:\\projects\\$SA_templates\\ddd16.db',2,4096,'C:\\projects\\$SA_templates\\ddd16.log'



1 comment:

Unknown said...

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!