By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,333 Members | 1,226 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,333 IT Pros & Developers. It's quick & easy.

case expression with SQL functions

P: n/a
How can I make Case expression not to execute ADDID() when it does not
satisfy requirements? Right now it executes function even if
requirments are not met.
SELECT ADDID(), CASE WHEN 'A' IS NULL THEN ADDID() END, ADDID() FROM
TABLE1;

Output:
1 2 3
----------- ----------- -----------
619 - 621

Antanas

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Antanas wrote:
How can I make Case expression not to execute ADDID() when it does not
satisfy requirements? Right now it executes function even if
requirments are not met.
SELECT ADDID(), CASE WHEN 'A' IS NULL THEN ADDID() END, ADDID() FROM
TABLE1;

Output:
1 2 3
----------- ----------- -----------
619 - 621


That was discussed recently here: DB2 will evaluate all branches of the CASE
expression, including the branches that are actually not used, i.e. the
THEN branch in your example. You can handle those cases by using an
approach like this:

SELECT addId(),
CASE WHEN 'A' IS NULL
THEN ( SELECT addId()
FROM sysibm.sysdummy1
WHERE 'A' IS NULL )
END,
addId()
FROM table1

Basically, you place a sub-select in the THEN branch, and that sub-select
returns the expression (call to addId()) that you need. Additionally, you
replicate the condition of the WHEN branch into the WHERE clause.

If the condition is more complex - it might call an expensive function
itself, - you could wrap it in a common table expression or nest it in
another CASE in a temp table in the FROM clause.
One basic question: it seems that you try to avoid gaps in the numbers
returned by the "addId" function. I assume that the function is still
based on sequences?
You are aware that DB2 does not guarantee that sequences will produce no
gaps in the numbers returned? Aborted transactions, cached sequence
numbers or concurrent transactions can lead to such gaps as well. So you
should check that you do not rely on such an "unreliable" property.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #2

P: n/a
Knut,

Yes, I am trying to avoid gaps in the numbers returned by the "addId"
function. Are you saying that sequences are unreliable, then what else
can I use to produce reliable id incrementation in single query?

Antanas

Nov 12 '05 #3

P: n/a
Antanas wrote:
Knut,

Yes, I am trying to avoid gaps in the numbers returned by the "addId"
function. Are you saying that sequences are unreliable, then what else
can I use to produce reliable id incrementation in single query?


Why can't you live with the gaps?

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Nov 12 '05 #4

P: n/a
>> I am trying to avoid gaps in the numbers returned by the "addId" function. Are you saying that sequences are unreliable, <<

No, they are simply non-relational since they are based on the internal
state of the hardware and not the data model. You can never use them
as a key by definition.
then what else can I use to produce reliable id incrementation in single query? <<


Think about how you handle your checkbook. You have the checks
preprinted and you track to whom and for what they are issued. Audit
trail!

Newbies keep looking for a "Magical, Universal Elixir" that will give
them keys without any research or work on their part. Get over it.
There is no magic. God did not put a 17-digit Hebrew number onthe
bottom of everything for you.

Nov 12 '05 #5

P: n/a
Try reading thread titled 'trigger fire at not null columns' dated Sept
29th.

Nov 12 '05 #6

P: n/a
Antanas wrote:
Knut,

Yes, I am trying to avoid gaps in the numbers returned by the "addId"
function. Are you saying that sequences are unreliable, then what else
can I use to produce reliable id incrementation in single query?

Antanas

This isn't a sequence problem. It's a problem of CASE expression.
Would you mind telling us the whole issue.
this thread is going on since a while and you are disclosing teh logic
one step at a time.. this is going to take a while this way... ;-)
So please provide the whole beast and we can (hopefully) help.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.