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
Post a Comment