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_]

On Mon, 19 May 2008 11:21:57 -0400, Serge Rielau <sr*****@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]

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 ODPR*ICEY 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(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1 +PMCOST6) AS
DEC(11,0))/(Sum(CFYARDS)*ODPR*ICEY)*100
(it may be neccesary to change 11 and 0 for DEC(p,s).)
or
(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMC OST6)*100.)/
(Sum(CFYARDS)*ODPR*ICEY)

[Dave Hughes]

jmoseby_@_elasticfabrics.com wrote:

On Mon, 19 May 2008 11:21:57 -0400, Serge Rielau <sr*****@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]

Plese try:
CAST(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1 +PMCOST6) AS
DEC(11,0))/(Sum(CFYARDS)*ODPR*ICEY)*100
(it may be neccesary to change 11 and 0 for DEC(p,s).)
or
(Sum(CFYARDS)*(PMCOST3+PMCOST4+PMCOST2+CRCOST1+PMC OST6)*100.)/
(Sum(CFYARDS)*ODPR*ICEY)

[Serge Rielau]

jmoseby_@_elasticfabrics.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