I'm trying to write a SELECT query that would add different conditions based on the existence of a connection-specific variable. If that variable exists, its value should be used, otherwise a different value. So I would like to use the VAREXISTS test within the same statement the variable's value is used:

-- optional varibale creation:
-- create variable varTest int = 1;

select varexists('varTest'),
   if varexists('varTest') = 1 then varTest else 'Value unknown' end if;

However, this returns SQLCODE -143 (Column 'varTest' not found) in case the variable does not exist, so it seems to be invalid to use the variable within the same statement as the VAREXISTS test.

In contrast, the following does work - but requires two distinct SELECT queries:

if varexists('varTest') = 1 then
   select varTest;
else
   select 'Value unknown';
end if;
in accordance with the doc's samples.

My attempt with the combination of testing and using the variable in one statement may be influenced by C-based short circuit tests, such as "if (pTest && pTest->SomeFunction())" because the language guarantees that the second condition will only be tested if the first condition is true. Of course, SQL has different rules for conditions, and there's no "short circuit logic" there so I do assume a statement refering to an non-existing column/variable must fail.

Is my assumption correct?

asked 25 Oct, 07:54

Volker%20Barth's gravatar image

Volker Barth
37.8k347515785
accept rate: 34%

edited 25 Oct, 08:49

> C-based short circuit tests

IMO that does allow undefined variables to be ignored

(26 Oct, 07:32) Breck Carter
Replies hidden

Well, AFAIK, the expression must also be valid (so name and type of a variable/expression must be known, so probably similar to the "annotability" required by SQL Anywhere) but the evaluation of the value itself is going to be skipped here.

Therefore I guess I have compared apples and oranges here. :)

(26 Oct, 08:25) Volker Barth

You cannot reference an object in a statement that does not exist because the statement gets fully annotated as part of execution. i.e. each symbol in the statement is matched to an object in the connection/database. See Annotation Phase in the docs: Query Processing Phases. Therefore you will need to use two distinct queries.

permanent link

answered 25 Oct, 10:44

Mark%20Culp's gravatar image

Mark Culp
24.5k9138291
accept rate: 41%

Thanks for the quick clarification, Mark!

Aside: Instead of two distinct (complex) queries I make use of some logic with an IF statement block to pre-calculate the condition, so I still only require one query.

(25 Oct, 12:57) Volker Barth
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:

×12
×2

question asked: 25 Oct, 07:54

question was seen: 105 times

last updated: 26 Oct, 08:25