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

db2 CASE or CAST problem

P: n/a
Who have a explication or solution
My Email in**@info-tact.com
Request #1 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
(nominal_total);

The result is 9.0 But the request return and error -801 (division by
zéro)

Request #2 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/dec(nominal_total) END as valeur FROM ( VALUES ( 0 ) )
as obs (nominal_total);

The result is good with not error

I tried with double(nominal_total) or int(nominal_total) and I received
the same error then request #1

Oct 18 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
ballo wrote:
Who have a explication or solution
My Email in**@info-tact.com
Request #1 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
(nominal_total);

The result is 9.0 But the request return and error -801 (division by
zéro)

Request #2 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/dec(nominal_total) END as valeur FROM ( VALUES ( 0 ) )
as obs (nominal_total);

The result is good with not error

I tried with double(nominal_total) or int(nominal_total) and I received
the same error then request #1
Which version of DB2 are you on?
There were issues in the past with DB2 being too aggressive with
constant expressions evaluation. In the latest V8 fixpacks these should
be fixed.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 18 '06 #2

P: n/a
Thank's for your help, My DB2 version is:

Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and
FixPak
"10".

Serge Rielau wrote:
ballo wrote:
Who have a explication or solution
My Email in**@info-tact.com
Request #1 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/nominal_total END as valeur FROM ( VALUES ( 0 ) ) as obs
(nominal_total);

The result is 9.0 But the request return and error -801 (division by
zéro)

Request #2 :
SELECT CASE WHEN nominal_total = 0 THEN 9.0 ELSE
10.00*1.00/dec(nominal_total) END as valeur FROM ( VALUES ( 0 ) )
as obs (nominal_total);

The result is good with not error

I tried with double(nominal_total) or int(nominal_total) and I received
the same error then request #1
Which version of DB2 are you on?
There were issues in the past with DB2 being too aggressive with
constant expressions evaluation. In the latest V8 fixpacks these should
be fixed.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 19 '06 #3

P: n/a
ballo wrote:
Thank's for your help, My DB2 version is:

Informational tokens are "DB2 v8.1.1.96", "s050811", "U803920", and
FixPak
"10".
I believe this has been fixed after FP10. I'll inquire.
What's happening here is that DB2's optimizer performs a few rules:
1. Constant pull up
nominal_total is replaced with 0
2. Constant folding
There is a (growing) list of functions which DB2 evaluates during
compilation of the query.
Obviously DEC() in FP 10 is not amongst them while double() and INT()
and "/"() are.
Now DB2 is supposed back of if any such compile time evaluation causes
an error (such as division by 0) and wait for runtime execution. But
it's not doing that for you.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Oct 19 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.