This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Sep 17, 2013 6:51 AM by user9542267 RSS

Missing Year in query

user9542267 Newbie
Currently Being Moderated

Hi All,

Here is my data sample:

create table activity(
activity_id number,
general_id number,
customer_id number,
fy number,
value1 number,
CONSTRAINT "PK_ACTIVITY" PRIMARY KEY (activity_id)
);

insert into activity(activity_id,general_id,customer_id,fy,value1)
values(1,1,1,2009,10);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(2,1,1,2010,20);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(3,1,1,2011,30);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(4,1,1,2012,40);

insert into activity(activity_id,general_id,customer_id,fy,value1)
values(5,1,2,2009,50);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(6,1,2,2010,60);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(7,1,2,2011,70);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(8,1,2,2012,80);

insert into activity(activity_id,general_id,customer_id,fy,value1)
values(9,2,1,2011,90);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(10,2,1,2012,100);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(11,2,1,2013,110);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(12,2,1,2014,120);
insert into activity(activity_id,general_id,customer_id,fy,value1)
values(13,2,1,2015,130);

Here is my select statement

select fy, value1,
    dense_rank() over (partition by general_id order by fy ) rnk
   from activity
    where general_id = 2
and fy between 2008 and 2016

I am pivoting the values but how can i display zero for the missing year 2008, 2009... ?

FY 2008 should be RNK 1 with value zero.

 

Thanks

  • 1. Re: Missing Year in query
    Sven W. Guru
    Currently Being Moderated

    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.

     

    example

     

      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
    



  • 2. Re: Missing Year in query
    Greg.Spall Expert
    Currently Being Moderated

    Why do you partition by general_id ?

    When you have it in the where clause, restricting it to just 1 anyway?

     

    [edit]

    nm, seems Sven beat me to the punch.   was working on same query, however, I was tripping over the partition ..

    [/edit]

  • 3. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Why do you partition by general_id ?

    Because I join this table with a general table but here i want to show just simple query.

     

    When you have it in the where clause, restricting it to just 1 anyway?

    Just to display one here.

  • 4. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Thanks guys for reply and help.

    I am getting ORA-32033: unsupported column aliasing

    Working with Oracle 11g.

  • 5. Re: Missing Year in query
    Sven W. Guru
    Currently Being Moderated

    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
  • 6. Re: Missing Year in query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

     

    user9542267 wrote:

     

    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 11.1.0.7.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

        FROM    dual

        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.

  • 7. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    That is my issue that activity table doesn't have all years. I am trying to add those missing years with zeros with programming code.

  • 8. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Thanks Frank for you reply and help.

    Sorry, i am using oracle 11g Edition 11.1.0.7.0

  • 9. Re: Missing Year in query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    user9542267 wrote:

     

    Thanks Frank for you reply and help.

    Sorry, i am using oracle 11g Edition 11.1.0.7.0

    Okay; CONNECT BY, as Sven and I suggested, works in your version.

  • 10. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Frank,

    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; 

    Result:

    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?

    Thanks

  • 11. Re: Missing Year in query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

    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.

  • 12. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Frank,

    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?

    Thanks !

  • 13. Re: Missing Year in query
    Frank Kulash Guru
    Currently Being Moderated

    Hi,

     

     

    user9542267 wrote:

     

    Frank,

    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?

    Thanks !

    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.

  • 14. Re: Missing Year in query
    user9542267 Newbie
    Currently Being Moderated

    Thanks Frank for your explanation !!

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points