Interactive SQL with division

[chelielynn]

Having problems with the below SQL. Running from an interactive session until it is working correctly but it currently is not returning the percent. Currently it is only return Zero's. Does anyone know what I am missing?

select decimal(percent,10,10), max, num from                     
    (select Decimal(num/max, 10,10) as percent, max, num         
          from (select sum(maxdoc) as max,                       
                sum(numdoc) as num                               
                from y07u999s)                                   
     as fold1)                                                   
as percent

Return values
DECIMAL MAX NUM
.0000000000 14,000,000 8,147,769

[uidamt98]

I think this is the most complex SQL statement which I met!
Give up!

[jamief]

Wow that is a huge one

All I can see is that if you divide num by max you get
.5819835 and you have your field defined as 10.

so WITHOUT multiplying by 100 to get 58%

the field would be all *Zeros


Not sure what the code would look like .....
Hope that helps maybe try some form of this

select Decimal((num/max*100), 4) as percent


Please post your solution when you get it

thanks
Jamie

[andy]

Re: Interactive SQL with division

I know this table, it's the folder balencing table from AWD's SMC databse.

I see what you trying to do and its this...

select (sum(numdoc) / sum(maxdoc) * 100) as Percent,
        sum(maxdoc) as Max,
        sum(numdoc) as Num
from y07u999s

I change our active folder every day with a batch job fldnam='volume' subdir='yyyy\mm\dd' and set maxdoc to all the 9's that way you'll never run out and it makes storage utilization stats easier because yous file system report is also a daily report on disk usage !

[andy]

Re: Interactive SQL with division

The above works on Oracle and MySQL but for DB2 you need the following (two decimal places).

select substr( char(sum(numdoc)/sum(float(maxdoc))*100),          
               1,                                                 
               locate('.',                                        
                      char(sum(numdoc)/sum(float(maxdoc))*100))+2 
                     ),                                           
sum(maxdoc) as Max,                                               
sum(numdoc) as Num                                                
from y07u999s

[littlepd]

Re: Interactive SQL with division

Isn't "Max" an SQL function? Won't you get into some trouble using that as a field name?

[andy]

Re: Interactive SQL with division

It is a function but a value following an "AS" is used as a literal to name or rename the column in the result set, even duplicates are permitted, ie.

select col1 as acolumn, col2 as acolumn
from atable

is ok too.

even this works...

select col1 as SELECT, col2 as FROM, col3 as WHERE ,
col4 as AND, col5 as HAVING, col6 as CURRENT
from table1

.. any reserved word is ok as long as you use the "AS"

[littlepd]

Re: Interactive SQL with division

Andy,

Thanks for clearing that up for me.

Michael