You need to outer join your activity table with a table that holds all the years. This second table can be constructed on the fly.
with years(yr) as (select 2008 from dual UNION ALL select yr+1 from years where yr < 2016) select * from years; YR 2008 2009 2010 2011 2012 2013 2014 2015 2016
Now we outer join with your table
with years(yr) as (select 2008 from dual UNION ALL select yr+1 from years where yr < 2016) select fy, value1, dense_rank() over (partition by a.general_id order by y.yr ) rnk from years y left join activity a on a.fy = y.yr and general_id = 2
Well you didn't mention your exact oracle version. Column aliasing in the with clause is available from 11.2 I think.
There are many alternatives. For example if your activity table holds all the needed years then select from there. If you do it properly, you might not even need an outer join. But correct grouping.
A connect by query is also possible.
with years as (select 2008+level-1 as yr from dual connect by level <= 2016-2008+1) select * from years
Thanks guys for reply and help.
I am getting ORA-32033: unsupported column aliasing
Working with Oracle 11g.
There's no version 11f or 11h, so it's kind of silly to say you have 11g. Why not say something like "I'm working with Oracle 184.108.40.206.0"?
Recursive WITH clauses, like Hoek used, work in Oracle 11.2 (and up).
Here's another way to get results like that:
WITH years AS
SELECT 2008 -- starting year
+ LEVEL - 1 AS yr
CONNECT BY LEVEL <= 9 -- number of years
This works in Oracle 9.1 (and up).
The values 2008 and 9 don't have to be hard-coded. Any kind of expressions, such as bind variables or scalar sub-queries, would work.
Why when I included other variable the rank is not the same?
with years as ( select 2008 + level - 1 as yr from dual connect by level <= 8 ) select y.yr, nvl(a.value1,0) as value1, dense_rank() over (partition by a.general_id order by y.yr ) rnk from years y left join activity a on a.fy = y.yr and a.general_id = 1 and a.customer_id = 1 order by y.yr;
YR VALUE1 RNK 1 2008 0 1 2 2009 10 1 3 2010 20 2 4 2011 30 3 5 2012 40 4 6 2013 0 2 7 2014 0 3 8 2015 0 4
Maybe the right question is how to included other variable in the dense_rank?
Sorry, I don't know what you want. I'm guessing that the results you posted are not what you want, but what are the right results? Post them, and explain how you get them from the sample data you posted in your first mesage. (Or post some different sample data if you need to.)
In any analytic function, such as DENSE_RANK, PARTITION BY x" means that each value of x is a world unto itself. It's as if you did a separate query, with a separate computation of the analytic function, for each value of x, and then UNIONed all the results together. Since you're saying "PARTITION BY a.general_id", then DENSE_RANK will assign a different set of values (1, 2, 3, ...) to each distinct value of a.general_id.
The only result that is not correct is the RNK column, it should be in order from 1 to 8. This happened after I included the a.customer_id column.
When I removed the partition clause from the dense_rank as "dense_rank() over ( order by y.yr ) rnk", it works... why?
As I said in the last message, "PARTITION BY a.general_id" is asking for a separate computation for each distinct value of a.general_id. In this case, a.general_id has 2 distinct values, 1 and 2, so DENSE_RANK looks only at the rows with a.general_id=1 and assigns one set of results (1, 2, 3 and 4) to those rows, then it looks only at the rows with a.general_id=2 and assigns another set of results (1, 2, 3 and 4 again).
The PARTITION BY clause is always optional; that is, any analytic function can work with or without a PARTITION BY clause.. If you want all rows in the result set to influence the function, then omit the PARTITION BY clause.