Re: SQL - Unexpected result

[Serge Rielau]

I give you a hint:
db2 =values 1/2;

1
-----------
0

1 record(s) selected.

db2 =values 1.0/2;

1
------------------------------------------
0.5

1 record(s) selected.

Let the slapping begin.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[jmoseby_@_elasticfabrics.com]

Re: SQL - Unexpected result

On Mon, 19 May 2008 11:21:57 -0400, Serge Rielau <srielau@ca.ibm .com>
wrote:

>I give you a hint:
>db2 =values 1/2;
>
>1
>-----------
0
>
1 record(s) selected.
>
>db2 =values 1.0/2;
>
>1
>------------------------------------------
0.5
>
1 record(s) selected.
>
>Let the slapping begin.
>

Serge,

Thanks for the quick reply. :)

I see in the documentation that if either operand of an arithmetic
operator is floating point, the operation is performed in floating
point. I assume that is what I am seeing here, right? So how would I
solve my problem? I need to be able to calculate a float value from
two integer values.

I apologize for these basic questions, but I am relatively new to DB2,
and some of the things I have taken for granted with other RDBs (like
the above problem) are not so simple with DB2.

Jm

[Tonkuma]

Re: SQL - Unexpected result

Allthough, you didn't give any DDL, my first impression was(maybe same
as Serge) that all CFYARDS, PMCOST3, PMCOST4, PMCOST2, CRCOST1,
PMCOST6, CFYARDS, and ODPRICEY might be integer and intermittent
results were integer, then lost decimal part.
If this guess was right, follwings might give you the answer.
CAST(Sum(CFYARD S)*(PMCOST3+PMC OST4+PMCOST2+CR COST1+PMCOST6) AS
DEC(11,0))/(Sum(CFYARDS)*O DPRICEY)*100
(it may be neccesary to change 11 and 0 for DEC(p,s).)
or
(Sum(CFYARDS)*( PMCOST3+PMCOST4 +PMCOST2+CRCOST 1+PMCOST6)*100. )/
(Sum(CFYARDS)*O DPRICEY)

[Dave Hughes]

Re: SQL - Unexpected result

jmoseby_@_elast icfabrics.com wrote:

On Mon, 19 May 2008 11:21:57 -0400, Serge Rielau <srielau@ca.ibm .com>
wrote:
>

I give you a hint:
db2 =values 1/2;

1
-----------
0

1 record(s) selected.

db2 =values 1.0/2;

1
------------------------------------------
0.5

1 record(s) selected.

Let the slapping begin.

Serge,
>
Thanks for the quick reply. :)
>
I see in the documentation that if either operand of an arithmetic
operator is floating point, the operation is performed in floating
point. I assume that is what I am seeing here, right? So how would I
solve my problem? I need to be able to calculate a float value from
two integer values.
>
I apologize for these basic questions, but I am relatively new to DB2,
and some of the things I have taken for granted with other RDBs (like
the above problem) are not so simple with DB2.

If you need a float value: DOUBLE(COL6)/COL9*100 would do it (FLOAT is
a synonym for DOUBLE if you want). If an integer percentage is
sufficient (COL6*100)/COL9 would be fine (assuming COL6 doesn't get so
large that multiplying by 100 would overflow a 32-bit signed integer).


Cheers,

Dave.

[Tonkuma]

Re: SQL - Unexpected result

Plese try:
CAST(Sum(CFYARD S)*(PMCOST3+PMC OST4+PMCOST2+CR COST1+PMCOST6) AS
DEC(11,0))/(Sum(CFYARDS)*O DPRICEY)*100
(it may be neccesary to change 11 and 0 for DEC(p,s).)
or
(Sum(CFYARDS)*( PMCOST3+PMCOST4 +PMCOST2+CRCOST 1+PMCOST6)*100. )/
(Sum(CFYARDS)*O DPRICEY)

[Serge Rielau]

Re: SQL - Unexpected result

jmoseby_@_elast icfabrics.com wrote:

I apologize for these basic questions, but I am relatively new to DB2,
and some of the things I have taken for granted with other RDBs (like
the above problem) are not so simple with DB2.

Hmm.. you use plural: rdbS. The only RDB where I'd expect to see a
difference is Oracle. But then in Oracle your column wasn't defined as
INTEGER. It was defined as NUMBER which is - not integer :-)
(If you specify INTEGER in Oracle you don't get INTEGER btw. You still
get NUMBER)

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab