1 2 Previous Next 17 Replies Latest reply: Sep 17, 2013 8:51 AM by user9542267 Go to original post RSS
      • 15. Re: Missing Year in query
        user9542267

        I have one more issue Frank,

        When I try to pivot the table to show the years as columns, I get two results because the general_id is blank or empty for the missing years.

        select general_id
        ,sum (case when rnk=1 then nvl(value1,0) else 0 end) value_1
        ,sum (case when rnk=2 then nvl(value1,0) else 0 end) value_2
        ,sum (case when rnk=3 then nvl(value1,0) else 0 end) value_3
        ,sum (case when rnk=4 then nvl(value1,0) else 0 end) value_4
        ,sum (case when rnk=5 then nvl(value1,0) else 0 end) value_5
        ,sum (case when rnk=6 then nvl(value1,0) else 0 end) value_6
        ,sum (case when rnk=7 then nvl(value1,0) else 0 end) value_7
        ,sum (case when rnk=8 then nvl(value1,0) else 0 end) value_8
        ,sum (case when rnk=9 then nvl(value1,0) else 0 end) value_9
        from (
          WITH  years  AS
          (
              SELECT  2008               -- starting year
                        + LEVEL - 1 AS yr
              FROM    dual
              CONNECT BY  LEVEL  <= 9    -- number of years
          )
          select general_id, y.yr, value1,  
             dense_rank() over (order by y.yr ) rnk  
          from years y  
          left join activity a on a.fy = y.yr --and general_id = 2 
          )
        --where  general_id is not  null
        group by general_id 
        

         

        Result:

        1   0    0    0    0    0    0    0    0    0

        2    1    0    60    80    100    120    0    0    0    0

        3    2    0    0    0    90    100    110    120    130    0

         

        Should be:

        1    1    0    60    80    100    120    0    0    0    0

        2    2    0    0    0    90    100    110    120    130    0

         

        how can I replace the blank or missing general_id?

        I try where  general_id is not  null but i am not sure if this is the correct way for my real huge query.

        Best,

        • 16. Re: Missing Year in query
          Frank Kulash

          Hi,

           

           

           

          user9542267 wrote:

           

          ...
          how can I replace the blank or missing general_id?

          I try where  general_id is not  null but i am not sure if this is the correct way for my real huge query.

          Best,

          That's the correct way for the query you posted; just un-comment the WHERE clause

           

          where  general_id is not  null

          If that's not producing the right results in some other query, or with some other data, then you'll have to post that query and/or data (simplified as much as possible, but still showing the same problem.)

          • 17. Re: Missing Year in query
            user9542267

            It is working fine, thanks again Frank!

            1 2 Previous Next