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 ?

asked 21 Feb, 05:00

Stalker's gravatar image

Stalker
298131734
accept rate: 12%

edited 21 Feb, 06:02

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716


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;
permanent link

answered 21 Feb, 05:46

Volker%20Barth's gravatar image

Volker Barth
33.8k330486716
accept rate: 33%

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, 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, 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, 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, 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, 11:09) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×136
×16

question asked: 21 Feb, 05:00

question was seen: 143 times

last updated: 22 Feb, 11:09