|
Hi All, SA 17.0.9.4913 The database declared variables. CREATE DATABASE VARIABLE "dba"."var1" INTEGER DEFAULT 1; CREATE DATABASE VARIABLE "dba"."var2" INTEGER DEFAULT 2; When creating a stored procedure, I write code in it: if nDocumentType = dba.var1 then message '1'; elseif nDocumentType = dba.var2 then message '1'; end if; The procedure is saved normally. If instead of "if" use "case" case nDocumentType when dba.var1 then message '1'; when dba.var2 then message '2'; end case; then when I try to save this procedure, I get the error message "Syntax error". Is this an error "SA 17" or am I doing something wrong ? |
|
AFAIK, this is by design. The CASE statement comes in two forms, and your are using the "CASE statement using value expressions", i.e. that one: CASE value-expression WHEN [ constant | NULL ] THEN statement-list ... [ WHEN [ constant | NULL ] THEN statement-list ] ... [ ELSE statement-list ] END [ CASE ] Apparently, it requires constants for comparison, so a varibale won't do. However, you can use variables in the other CASE form, such as: case when nDocumentType = dba.var1 then message '1' when nDocumentType = dba.var2 then message '2' end case; 1
> it requires constants for comparison ...well, that defies The Watcom Rule :) Fortunately, that restriction only applies to CASE statements, not CASE expressions which can have expressions as the WHEN values.
(21 Feb '19, 13:36)
Breck Carter
Replies hidden
I see, thanks for the explanation. P.S. By the way, it would be nice for the command "CREATE DATABASE VARIABLE" to add the option "CONST" - the flag of the notation for the constant.
(22 Feb '19, 02:55)
Stalker
Replies hidden
FWIW, a request to add the notion for adding constants has been made several years ago (here, for local variables, not database-scoped ones): Add the possibility to declare constants within blocks However, you can make some kind of "read-only" database variable by using according access privileges, see that answer.
(22 Feb '19, 03:22)
Volker Barth
That's an interesting (or suprising?) distinction, and gladly I'm using CASE expressions way more often then CASE statements...
(22 Feb '19, 03:23)
Volker Barth
> using CASE expressions way more often Me too... CASE statements have limited use as standalone procedural statements, whereas CASE expressions can appear throughout complex expressions in all forms of SQL statements, especially all the SELECT clauses (SELECT list, FROM, WHERE, ORDER BY and so on).
(22 Feb '19, 11:09)
Breck Carter
|