Version: 11g
Hi There,
Yesterday got some great help from Frank and Al regarding a query to get the % increase from previous year
2471454
One problem that I am facing with this is if the sepcific "exp_type" does not exists in the table for a specific year, I get the " divide by zero error.
It works great when there are values for all years. Is there a way to work around this? Maybe show the value as null or 0 but not throw an error. I tried using NVL but did not work
{code}
WITH total AS
(
SELECT TO_CHAR(exp_date,'YYYY') yr
, SUM(CASE WHEN exp_type = :P1_YOY_PERCENT_LIST THEN exp_amt ELSE 0 END) AS Type
FROM exp_main
GROUP BY TO_CHAR(exp_date,'YYYY')
)
SELECT yr "Year"
, Type
, TO_CHAR((Type / NVL(LAG(Type) OVER (ORDER BY yr), Type) * 100)-100,'999990')||'%' "%increase"
FROM total
ORDER BY yr DESC
{code}
FYI- The :P1_YOY_PERCENT_LIST is just a value in Apex that I am using to enable users to select any "exp_type" for which they want to view the % increase from previous year.
Thanks,
Ryan
Edited by: ryansun on Nov 29, 2012 12:37 AM
Edited by: ryansun on Nov 29, 2012 1:02 AM