sql - Percentage is equal to zero -


please me, got stuck in error "ora-01476: divisor equal zero" , thing, when value of column 1 (g.ticket) or column 2 (h.employee) 0 1 in ration instead 0. how solve that? thank you. appreciate help.

query:

    select 'data'     ||','||to_char(d.dtime_day, 'mm/dd/yyyy')     ||','||nvl(g.ticket, 0 )     ||','||round((nvl(g.ticket,2) / nvl(h.employee,2)),2)     ||','||nvl(h.employee,0)     ||','||round((nvl(h.employee,2) / nvl(g.ticket,2)),2)     owner_dwh.dc_date d     left join (         select trunc(t.create_time,'iw') ddate               ,count(case when upper(ticket_customer_user_id) = upper('hcphuser') 1   else null end) ticket         app_account.otrs_ticket t          t.create_time between to_date('02/20/2013','mm/dd/yyyy') , sysdate -1         , queue_id in (4,6,8,9,10,12,13,14,15,16,20,27,28,30,31,32,35,36,37,38,39,40,44,45,46,47,50,52,53,55,56)         group trunc(t.create_time,'iw')     ) g on d.dtime_day = g.ddate     left join (        select trunc(e.date_hired, 'iw') hdate              ,count(e.date_hired) employee        app_account.hr_employee e        e.date_hired between to_date('02/20/2013','mm/dd/yyyy') , sysdate -1        group trunc(e.date_hired, 'iw')    ) h on d.dtime_day = h.hdate    d.dtime_day between to_date('02/20/2013','mm/dd/yyyy') , trunc(sysdate) -1    , trunc(d.dtime_day, 'iw')= d.dtime_day    order d.dtime_day; 

little unclear want happen should able avoid getting ora-01476 using case expression. please try this:

 select 'data'     ||','||to_char(d.dtime_day, 'mm/dd/yyyy')     ||','||nvl(g.ticket, 0 )     ||','||case when nvl(h.employee,0) = 0 0                  else round((nvl(g.ticket,0) / h.employee),2) end     ||','||nvl(h.employee,0)     ||','||case when nvl(g.ticket,0) = 0 0                  else round((nvl(h.employee,0) / g.ticket),2) end     owner_dwh.dc_date d <rest of statement>  

Comments

Popular posts from this blog

Android layout hidden on keyboard show -

google app engine - 403 Forbidden POST - Flask WTForms -

c - Why would PK11_GenerateRandom() return an error -8023? -