case statement - strange results

From: stephen O'D <stephen.odonnell_at_gmail.com>
Date: 23 Aug 2005 14:20:09 -0700
Message-ID: <1124832009.187409.202580@z14g2000cwz.googlegroups.com>

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. Received on Tue Aug 23 2005 - 16:20:09 CDT