Conference orarep::nomahs::sql

3877.0. "case & count(distinct... from view" by ukvms3.uk.oracle.com::LWILES (Louise Wiles, UK Rdb support) Tue Apr 08 1997 06:32

    Hi,

    Rdb V6.1-1
    VMS V6.1

    A customer of mine is getting a problem with case & count(distinct - it
    appers to be returning the wrong number of rows.

    They are doing a select count(distinct from a view, where the view has
    a case statement, and the last record seems to be duplicated:

    SQL> select counter_by, count(distinct complaint_ref)
    cont> from comps
    cont> group by counter_by;
     COUNTER_BY
     Jones                  2
     UK                     2
     Wiles                  1
     Wiles                  1
    4 rows selected

    The SQL below reproduces this.

    Does this look right?

    Thanks,
    Louise.

    create database filename cathy_db;
    create table complaints (
        location char(10),
        complaint char(10),
        responsibility char(1),
    	counter_on date vms,
    	authorized_on date vms,
    	counter_at char(10),
    	authorization_at char(10),
    	counter_by char(10),
    	authorization_by char(10));

    create view comps (
    	complaint_ref,
    	counter_on,
    	counter_at,
    	counter_by)
    as
     select
              ( (location || '/') || 
    		substring(('000000' || cast(complaint as varchar(7))) 
                         from 1+(octet_length
                                 (cast(complaint as varchar(7))) -1) for 7)),
              case
                  when responsibility = 'R'
                  then counter_on
                  else authorized_on
              end,
              case
                  when responsibility = 'R'
                  then counter_at
                  else authorization_at
              end,
              case
                  when responsibility = 'R'
                  then counter_by
                  else authorization_by
              end
              from complaints;

    insert into complaints values (
    '1234567890',
    'AAAAAAAAAA',
    'R',
    '1-JUN-1966',
    '2-JUN-1966',
    'Newbury',
    'Berks',
    'Wiles',
    'Smith');

    insert into complaints values (
    '0987654321',
    'BBBBBBBBBB',
    'R',
    '17-MAR-1966',
    '8-MAR-1966',
    'Lincoln',
    'Lincs',
    'France',
    'Jones');

    insert into complaints values (
    '0987654321',
    'BBBBBBBBBB',
    'R',
    '17-MAR-1966',
    '8-MAR-1966',
    'John',
    'AA',
    'France',
    'Jones');

    insert into complaints values (
    '0987654321',
    'BBBBBBBBBB',
    'R',
    '17-MAR-1966',
    '8-MAR-1966',
    'John',
    'AA',
    'France',
    'Jones');

    insert into complaints values (
    '2468013579',
    'BBBBBBBBBB',
    'S',
    '17-MAR-1966',
    '8-MAR-1966',
    'John',
    'AA',
    'France',
    'Jones');

    insert into complaints values (
    '2468013579',
    'CCCCCCCCCC',
    'S',
    '17-MAR-1966',
    '8-MAR-1966',
    'John',
    'AA',
    'France',
    'Jones');

    insert into complaints values (
    '2468013579',
    'CCCCCCCCCC',
    'R',
    '21-MAR-1966',
    '2-MAR-1966',
    'John',
    'AA',
    'France',
    'Jones');

    insert into complaints values (
    '2468013579',
    'ZZZZZZZZZZ',
    'R',
    '21-MAR-1966',
    '2-MAR-1966',
    'John',
    'AA',
    'UK',
    'Jones');


    insert into complaints values (
    '1234567890',
    'AAAAAAAAAA',
    'R',
    '9-JUN-1996',
    '10-JUN-1996',
    'Reading',
    'Berks',
    'Wiles',
    'Smith');

    select counter_by, count(distinct complaint_ref)
    from comps
    group by counter_by;

     COUNTER_BY
     Jones                  2
     UK                     2
     Wiles                  1
     Wiles                  1
    4 rows selected

Yes there is something wrong here.  Please submit a BUG report and reference
this note.

As you can see the COUNT(DISTINCT) seems to be causing the returned data for
the group to be incorrect (as well as not returning the correct counts).

Ian

SQL>     select distinct counter_by
cont>     from comps
cont>     ;
 COUNTER_BY   
 France       
 Jones        
 UK           
 Wiles        
4 rows selected
SQL> 
SQL>     select counter_by
cont>     from comps
cont>     group by counter_by
cont>     ;
 COUNTER_BY   
 France       
 Jones        
 UK           
 Wiles        
4 rows selected
SQL> 
SQL>     select counter_by, count (*)
cont>     from comps
cont>     group by counter_by
cont>     ;
 COUNTER_BY                 
 France                 4   
 Jones                  2   
 UK                     1   
 Wiles                  2   
4 rows selected
SQL> 
SQL>     select counter_by, count(distinct complaint_ref)
cont>     from comps
cont>     group by counter_by
cont>     ;
 COUNTER_BY                 
 Jones                  2   
 UK                     2   
 Wiles                  1   
 Wiles                  1   
4 rows selected
SQL> 
SQL>     select *
cont>     from comps
cont>     order by counter_by
cont>     ;
 COMPLAINT_REF              COUNTER_ON                COUNTER_AT   COUNTER_BY   
 0987654321/BBBBBBB         17-MAR-1966 00:00:00.00   John         France       
 0987654321/BBBBBBB         17-MAR-1966 00:00:00.00   John         France       
 2468013579/CCCCCCC         21-MAR-1966 00:00:00.00   John         France       
 0987654321/BBBBBBB         17-MAR-1966 00:00:00.00   Lincoln      France       
 2468013579/CCCCCCC          8-MAR-1966 00:00:00.00   AA           Jones        
 2468013579/BBBBBBB          8-MAR-1966 00:00:00.00   AA           Jones        
 2468013579/ZZZZZZZ         21-MAR-1966 00:00:00.00   John         UK           
 1234567890/AAAAAAA          1-JUN-1966 00:00:00.00   Newbury      Wiles        
 1234567890/AAAAAAA          9-JUN-1996 00:00:00.00   Reading      Wiles        
9 rows selected
SQL> 
SQL> rollback;

    See BUG #476557