Re: case statement - strange results

stephen O'D wrote:
> I am trying to use the CASE statement on oracle 9.2.0.4 on Redhat
> (intel) and 9.2.0.6 on PA_RISC HPUX and seem to get a strange error in
> a certain case. Either I am doing something very silly, or there is a
> bug in Oracle. Here is my test case:-
>
> create table mytest (account_num varchar2(40),
> event_seq integer,
> event_type_id integer,
> attr_2 varchar2(50),
> attr_11 varchar2(50));
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 55,
> 'foobar',
> '00:01:01');
>
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
>
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
>
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
>
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
>
> insert into mytest values ('myacc001',
> 1,
> 56,
> '10',
> 'foobar');
>
> commit;
>
> Now, for event_type_id 55, the value in attr_11 corresponds to a time
> (HH:MI:SS) - I know this is not a good way to store it, but its a
> vendor application. I need to get a total number of seconds for each
> event_type_id, event_seq group. For event_type_id 56, attr_2 is the
> value of interest and I want to sum them up too. Simple I thought:-
>
> select event_seq,
> event_type_id,
> case
> when event_type_id in (55) then
> sum ( to_number( substr( attr_11,7,2 ) )
> +
> to_number( substr( attr_11,4,2 ) ) * 60
> +
> to_number( substr( attr_11,1,2 ) ) * 60 * 60
> )
> when event_type_id in (56) then
> sum ( to_number( attr_2 ) )
> else
> sum(1)
> end stuff
> from mytest
> group by event_seq, event_type_id
>
> SQL> @mysql
> SQL> /
> sum ( to_number( attr_2 ) )
> *
> ERROR at line 12:
> ORA-01722: invalid number
>
> So after trying to work out where this mysterious 'not a number error'
> for an hour or two, I tried the same query using a decode:-
>
> select event_seq,
> event_type_id,
> sum (decode (event_type_id, 55, ( to_number( substr(
> attr_11,7,2 ) )
> +
> to_number( substr( attr_11,4,2 ) ) * 60
> +
> to_number( substr( attr_11,1,2 ) )
> * 60 * 60 ),
> 56, to_number( attr_2 ),
> 1) ) total
> from mytest
> group by event_seq, event_type_id
>
> SQL> @mysql2
> SQL> /
>
> EVENT_SEQ EVENT_TYPE_ID TOTAL
> ---------- ------------- ----------
> 1 55 366
> 1 56 60
>
> SQL>
>
>
> So it works as I thought it would with DECODE.
>
> Can anyone else reproduce this error on 9i (or better get it work), or
> am I doing something very stupid?
>
> Thanks,
>
> Stephen.

I didn't spend any time looking at your example after I saw you doing to things that are clear demonstrations of bad design.

1. Columns named attr_2 and attr_11.
2. Using VARCHAR2 to hold time.

My recommendation is that before you kludge this together you:

1. Take a basic class in normalization and database design.
2. Learn how to use a column of DATE data type.

My apology if this seems harsh but I wouldn't let one of my students get past their first midterm with a design like this.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)