SA 17.0.9.4913: Not work "database variable" in "case"

[former_SQLA_member1694881]

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 ?

[VolkerBarth · Accepted solution]

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;

[Breck_Carter · Accepted solution]

> 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.

[former_SQLA_member1694881 · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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.

[VolkerBarth · Accepted solution]

That's an interesting (or suprising?) distinction, and gladly I'm using CASE expressions way more often then CASE statements...

[Breck_Carter · Accepted solution]

> 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).