1 2 Previous Next 17 Replies Latest reply: Sep 29, 2005 6:28 AM by Lucas Jellema RSS

    Group Above Report problem...not able to display absent rows in date range

    Jason ORCL
      In my report query, I have numerous select statements that I must union together and group by a date. The ultimate goal is to get a count for all records in coordination with that date. Then in the report, I group all the data with the same date to ultimately put all counts on a page that coordinate with a single date.
      Thing is...if there are no records associated with that date, the 'group by' clause makes the return data set not include those lines of output. I need these lines of output! Any suggestions??

      Below is an example...


      Select 'Program1', 'SubProgram1', 'Label 1', to_char(receipt_date, 'MM/DD/RRRR'), count(*)
      from submissions
      where receipt_date between '01-JUN-05' and '10-JUN-05'
      and substr(submission_index,8,1) = '3'
      and substr(submission_index,9,1) = '1'
      group by substr(submission_index,8,1), substr(submission_index,9,1), to_char(receipt_date, 'MM/DD/RRRR')

      union

      Select 'Program2', 'SubProgram2', 'Label 2',
      to_char(receipt_date, 'MM/DD/RRRR'), count(*)
      from submissions
      where receipt_date between '01-JUN-05' and '10-JUN-05'
      and substr(submission_index,8,1) = '4' group by substr(submission_index,8,1), to_char(receipt_date, 'MM/DD/RRRR')



      OUTPUT:

      Program1 SubProgram1 Label 1 06/01/2005 4
      Program1 SubProgram1 Label 1 06/02/2005 2
      Program1 SubProgram1 Label 1 06/03/2005 6
      Program1 SubProgram1 Label 1 06/04/2005 23
      Program1 SubProgram1 Label 1 06/06/2005 71
      Program1 SubProgram1 Label 1 06/07/2005 245
      Program1 SubProgram1 Label 1 06/08/2005 76
      Program1 SubProgram1 Label 1 06/10/2005 45
      Program2 SubProgram2 Label 2 06/01/2005 66
      Program2 SubProgram2 Label 2 06/02/2005 345
      Program2 SubProgram2 Label 2 06/03/2005 89
      Program2 SubProgram2 Label 2 06/04/2005 12
      Program2 SubProgram2 Label 2 06/05/2005 3
      Program2 SubProgram2 Label 2 06/06/2005 27
      Program2 SubProgram2 Label 2 06/09/2005 98
      Program2 SubProgram2 Label 2 06/10/2005 456

      I need the missing lines to show up for Program 1 on 6/05 and 6/09
      and Program 2 on 6/07 and 6/08
        • 1. Re: Group Above Report problem...not able to display absent rows in date range
          452095
          I'm not sure if I'll be able to solve your problem or not... but do you just want to see the summary (what you have posted)? Or does your report need to show the details also (Program 1 on 6/1 for example would have 4 detail rows)?
          • 2. Re: Group Above Report problem...not able to display absent rows in date range
            Jason ORCL
            Just the summary...the count of the rows, as I show
            • 3. Re: Group Above Report problem...not able to display absent rows in date ra
              Lucas Jellema
              It seems quite natural that the query will not produce any results for dates that are actually in either of the two tables. How would the database know you want records for dates that you do not have records for?

              What you can do is use an in-line view that returns records for all dates that you are interested in, and outer join join that with the submissions table. Something like:

              Select 'Program1', 'SubProgram1', 'Label 1', to_char(receipt_date, 'MM/DD/RRRR'), count(*)
              from submissions
              , (select days.day + to_date('01-JUN-05','DD-MON-YY') date_value
              from ( select rownum day from dual connect by rownum < 11 ) days
              ) dates
              where receipt_date= dates.date_value'
              and substr(submission_index,8,1) = '3'
              and substr(submission_index,9,1) = '1'
              group by substr(submission_index,8,1), substr(submission_index,9,1), to_char(receipt_date, 'MM/DD/RRRR')
              /
              Good luck,

              Lucas
              • 4. Re: Group Above Report problem...not able to display absent rows in date ra
                452095
                Using Lucas' query you could replace:

                Select 'Program1', 'SubProgram1', 'Label 1', to_char(receipt_date, 'MM/DD/RRRR'), count(*)
                from submissions
                , (select days.day + to_date('01-JUN-05','DD-MON-YY') date_value
                from ( select rownum day from dual connect by rownum < 11 ) days

                ) dates
                where receipt_date= dates.date_value'
                and substr(submission_index,8,1) = '3'
                and substr(submission_index,9,1) = '1'
                group by substr(submission_index,8,1), substr(submission_index,9,1), to_char(receipt_date, 'MM/DD/RRRR')

                ...with:
                select  (to_date(to_char(sysdate),'DD-MON-YY') - days.day) date_value
                from    (   select  rownum day
                            from    dual
                            connect by rownum < 11
                        ) days
                ...which will give you the ten previous days, or you could replace 'to_char(sysdate)' with a user supplied date for your report.

                DATE_VALUE
                9/27/2005
                9/26/2005
                9/25/2005
                9/24/2005
                9/23/2005
                9/22/2005
                9/21/2005
                9/20/2005
                9/19/2005
                9/18/2005
                • 5. Re: Group Above Report problem...not able to display absent rows in date ra
                  Jason ORCL
                  yes, that makes sense...but if you join a table with all the dates to a table that has no rows in some of those dates, and you join it where the dates are equal, you still dont get zero counts for those dates!

                  Any suggestions how to fix this?
                  • 6. Re: Group Above Report problem...not able to display absent rows in date ra
                    452095
                    I'm not exactly sure what you are worried about, but I'll give it a whirl. If a date has a pair than a count value should come along with it, if a date does not have a match than just set the null value equal to 0. Does this answer your question?
                    • 7. Re: Group Above Report problem...not able to display absent rows in date ra
                      Jason ORCL
                      If i run the above query, I still do not get any rows returned for the dates I have no info for. I need a row of output to show a 0 count. if there are no rows that are returned, and i use a group by, there is no zero count...no null either
                      • 8. Re: Group Above Report problem...not able to display absent rows in date ra
                        Lucas Jellema
                        Sorry, my mistake: you need an outer join. Something like:

                        from submissions
                        right outer join
                        , (select days.day + to_date('01-JUN-05','DD-MON-YY') date_value
                        from ( select rownum day from dual connect by rownum < 11 ) days
                        ) dates
                        on (receipt_date= dates.date_value')
                        where ....

                        The outer join ensures that you will at least get one record for every date_value, even if none of the submission match on date.

                        regards,

                        Lucas
                        • 9. Re: Group Above Report problem...not able to display absent rows in date ra
                          452095
                          create table your_table (
                              program     varchar2(20),
                              subprogram  varchar2(20),
                              label       varchar2(20),
                              day         date,
                              num         number
                              );
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('01-JUN-05'), 4); 
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('02-JUN-05'), 2);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('03-JUN-05'), 6);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('04-JUN-05'), 23);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('06-JUN-05'), 71);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('07-JUN-05'), 245);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('08-JUN-05'), 76);
                          insert into your_table values ('Program1', 'SubProgram1', 'Label 1', to_date('10-JUN-05'), 45);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('01-JUN-05'), 66);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('02-JUN-05'), 345);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('03-JUN-05'), 89);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('04-JUN-05'), 12);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('05-JUN-05'), 3);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('06-JUN-05'), 27);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('09-JUN-05'), 98);
                          insert into your_table values ('Program2', 'SubProgram2', 'Label 2', to_date('10-JUN-05'), 456);
                          SELECT     yt.program
                          ,           yt.subprogram
                          ,           yt.label
                          ,           nvl(yt.num,0)     NUM
                          ,           days.date_value
                          FROM     (     select  (to_date('11-JUN-05','DD-MON-YY') - day) date_value
                                         from    (   select  rownum day            
                                                     from    dual            
                                                     connect by rownum < 11        
                                                   )      
                                    )                    days
                          ,          your_table          yt
                          where   yt.day(+) = days.date_value
                          order by yt.program, days.date_value
                          PROGRAM          SUBPROGRAM     LABEL     NUM     DATE_VALUE
                          Program1     SubProgram1     Label 1     4     6/1/2005
                          Program1     SubProgram1     Label 1     2     6/2/2005
                          Program1     SubProgram1     Label 1     6     6/3/2005
                          Program1     SubProgram1     Label 1     23     6/4/2005
                          Program1     SubProgram1     Label 1     71     6/6/2005
                          Program1     SubProgram1     Label 1     245     6/7/2005
                          Program1     SubProgram1     Label 1     76     6/8/2005
                          Program1     SubProgram1     Label 1     45     6/10/2005
                          Program2     SubProgram2     Label 2     66     6/1/2005
                          Program2     SubProgram2     Label 2     345     6/2/2005
                          Program2     SubProgram2     Label 2     89     6/3/2005
                          Program2     SubProgram2     Label 2     12     6/4/2005
                          Program2     SubProgram2     Label 2     3     6/5/2005
                          Program2     SubProgram2     Label 2     27     6/6/2005
                          Program2     SubProgram2     Label 2     98     6/9/2005
                          Program2     SubProgram2     Label 2     456     6/10/2005
                          The four null rows will not show up because the date is used at least once between program 1 or program 2. Will there only ever be two programs to chose from? Any one else got any ideas around this?
                          • 10. Re: Group Above Report problem...not able to display absent rows in date ra
                            Lucas Jellema
                            Are you on 10g? In that case the PARTITION OUTER JOIN for this. See the article http://five.pairlist.net/pipermail/oracle-article/2003/000005.html for details. Basically the partitiomn outer join allows you to have an outer join per partition, where in your case partition is defined by program.

                            Lucas
                            • 11. Re: Group Above Report problem...not able to display absent rows in date ra
                              452095
                              Unfortunately the database version I'm using is 9i. Any other suggestions?
                              • 12. Re: Group Above Report problem...not able to display absent rows in date ra
                                Lucas Jellema
                                What you could try to do is something like this:

                                If you do not happen to be on 10g just yet, you can simulate the Partition Outer Join with code like this:
                                <pre>with years as
                                ( select 1979 + years.year year
                                from ( select rownum year
                                from dual
                                connect
                                by rownum < 6 ) years
                                )
                                select depts.deptno
                                , years.year hireyear
                                , count(empno)
                                from (select distinct deptno
                                from emp
                                ) depts
                                cross join
                                years
                                left outer join
                                emp staff
                                on (extract (year from staff.hiredate) = years.year and depts.deptno = staff.deptno)
                                group
                                by depts.deptno
                                , years.year
                                order
                                by depts.deptno
                                , hireyear
                                </pre>
                                Here I have cross joined a list of all departments with the list of all years. That means that I have set up as yet empty cells for every combination of department (deptno) and year. I then left outer join that grid of empty cells with the actual summary results of aggregating over emp. This will fill in as many cells as possible.

                                This suggestion is an excerpt from a small article I just wrote: Summary results for all dates, including the ones that I do not have data for - example of using Partition Outer Join - Oracle10g SQL Feature at http://technology.amis.nl/blog/index.php?p=819.

                                Good luck.
                                Lucas
                                • 13. Re: Group Above Report problem...not able to display absent rows in date range
                                  455696
                                  What u need is to generate rows that do not exist in the database. Oracle provides a feature to solve this kind of problems i.e. functional tables.

                                  1- Create functional table:
                                  create type date_type as object
                                  (vdate date)
                                  /

                                  create type date_obj_type as table of date_type;
                                  /

                                  create or replace function get_days_between(pd1 date,pd2 date) return date_obj_type as
                                  v_tab date_obj_type:=date_obj_type();
                                  v_date date;
                                  v_n number;
                                  begin
                                  v_n:=pd2-pd1;
                                  for r in 1..v_n loop
                                  v_tab.extend;
                                  v_tab(v_tab.last):=date_type(pd1+r);
                                  end loop;
                                  return v_tab;
                                  end;
                                  /

                                  2- Create a query in report:

                                  SELECT * FROM TABLE(get_days_between(:d1,:d2))

                                  where :D1 and :D2 are your report date range parameters.

                                  3- Link your other query to this one on date.

                                  Anwar
                                  • 14. Re: Group Above Report problem...not able to display absent rows in date range
                                    Laurent Schneider
                                    Lucas,
                                    As I wrote in my blog, connect by without prior is a hack, it is not supposed to work, it does not mean anything. it is pure hazard.

                                    Regards
                                    Laurent
                                    SYS@LSC69 AS SYSDBA/7.3.4.5
                                    SQL> select rownum from dual connect by rownum<5;
                                    ERROR:
                                    ORA-01436: CONNECT BY loop in user data
                                    
                                    SYS@LSC70 AS SYSDBA/8.1.7.4
                                    SQL> select rownum from dual connect by rownum<5;
                                        ROWNUM
                                    ----------
                                             1
                                    
                                    SYS@LSC73 AS SYSDBA/9.2.0.7
                                    SQL> select rownum from dual connect by rownum<5;
                                        ROWNUM
                                    ----------
                                             1
                                    
                                    SYS@LSC62 AS SYSDBA/10.1.0.4
                                    SQL> select rownum from dual connect by rownum<5;
                                        ROWNUM
                                    ----------
                                             1
                                             2
                                             3
                                             4
                                             5
                                    
                                    SYS@LSC63 AS SYSDBA/10.2.0.1
                                    SQL>  select rownum from dual connect by rownum<5;
                                        ROWNUM
                                    ----------
                                             1
                                             2
                                             3
                                             4
                                    1 2 Previous Next