Easy! With the first statement, you're converting physical_mb to TB
(roughly, 1048576 is the "right" value), then adding them up. For the 2nd
one, you're converting to TB, cutting it off at the 10GB level (the cast to
decimal(6,2) does that), then adding them up. So the 2nd one is dropping
lots of data - any filesystem not over 10GB of data really isn't being
counted at all.
I think this is what you're looking for:
select cast(sum(physical_mb)/1000000 as decimal(6,2)) as "Total TB" from
occupancy
That one sums up physical_mb from occupancy, then divides it by the 100000
for TB, then casts it to the format you want.
Nick Cassimatis
nickpc AT us.ibm DOT com
He who laughs last has a good backup.
Curtis Stewart
<Curtis.Stewart@L
AWSON.COM> To
Sent by: "ADSM: ADSM-L AT VM.MARIST DOT EDU
Dist Stor cc
Manager"
<[email protected] Subject
.EDU> Weird Select Behavior
08/05/2004 01:38
PM
Please respond to
"ADSM: Dist Stor
Manager"
Hi everyone. I've been struggling with this for a time now and can't seem
to come up with an answer on my own. I hope one of you might be able to
explain it to me.
When I run a simple sum select statement from the physical_mb field of the
occupancy table I get one answer. However, when I run the same select, but
cast the result as decimal I get a wildly different result. I expected it
to just format the output and get rid of some of the trailing decimal
places. I looked at the syscat.columns occupancy stuff and it says the
physical_mb field is decimal, so I don't understand the behavior.
Obviously, there's something I just don't know about the select behavior.
Here are the two scripts and their output. Any help is appreciated.
tsm: MSPTSM01COL>select sum(physical_mb/1000000) from occupancy
Unnamed[1]
---------------------------------
70.29775000000000000000000
tsm: MSPTSM01COL>
tsm: MSPTSM01COL>select sum(cast (physical_mb/1000000 as decimal(6,2))) as
"Total TB" from occupancy
Total TB
---------------------------------
66.17
tsm: MSPTSM01COL>
curtis.stewart AT lawson DOT com
|
