Re: case statement - strange results

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 23 Aug 2005 17:20:04 -0700
Message-ID: <1124842804.111242.254250@o13g2000cwo.googlegroups.com>

scott_at_ORA92> -- test table and data:
scott_at_ORA92> desc mytest

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ACCOUNT_NUM                                        VARCHAR2(40)
 EVENT_SEQ                                          NUMBER(38)
 EVENT_TYPE_ID                                      NUMBER(38)
 ATTR_2                                             VARCHAR2(50)
 ATTR_11                                            VARCHAR2(50)

scott_at_ORA92> column account_num format a11
scott_at_ORA92> column event_seq	format 999999999
scott_at_ORA92> column attr_2	format a15
scott_at_ORA92> column attr_11	format a15

scott_at_ORA92> select * from mytest
  2 /

ACCOUNT_NUM EVENT_SEQ EVENT_TYPE_ID ATTR_2 ATTR_11 ----------- ---------- ------------- --------------- ---------------

myacc001             1            55 foobar          00:01:01
myacc001             1            55 foobar          00:01:01
myacc001             1            55 foobar          00:01:01
myacc001             1            55 foobar          00:01:01
myacc001             1            55 foobar          00:01:01
myacc001             1            55 foobar          00:01:01
myacc001             1            56 10              foobar
myacc001             1            56 10              foobar
myacc001             1            56 10              foobar
myacc001             1            56 10              foobar
myacc001             1            56 10              foobar
myacc001             1            56 10              foobar

12 rows selected.

scott_at_ORA92> -- reproduction of error:
scott_at_ORA92> select event_seq,

  2  	    event_type_id,
  3  	    case
  4  	      when event_type_id in (55) then
  5  		sum ( to_number( substr( attr_11,7,2 ) )
  6  		      +
  7  		      to_number( substr( attr_11,4,2 ) ) * 60
  8  		      +
  9  		      to_number( substr( attr_11,1,2 ) ) * 60 * 60
 10  		    )
 11  	      when event_type_id in (56) then
 12  		sum ( to_number( attr_2 ) )
 13  	      else
 14  		sum(1)
 15  	      end stuff

 16 from mytest
 17 group by event_seq, event_type_id
 18 /

           sum ( to_number( attr_2 ) )
                 *

ERROR at line 12:
ORA-01722: invalid number

scott_at_ORA92> -- correction:
scott_at_ORA92> select event_seq, event_type_id,

  2  	    sum (case when event_type_id in (55)
  3  		      then to_number (substr (attr_11, 7, 2))
  4  			   + to_number (substr (attr_11, 4, 2)) * 60
  5  			   + to_number (substr (attr_11, 1, 2)) * 60 * 60
  6  		      when event_type_id in (56)
  7  		      then to_number (attr_2)
  8  		      else 1
  9  		 end) as stuff

 10 from mytest
 11 group by event_seq, event_type_id
 12 /

 EVENT_SEQ EVENT_TYPE_ID STUFF
---------- ------------- ----------

         1            55        366
         1            56         60

scott_at_ORA92> Received on Tue Aug 23 2005 - 19:20:04 CDT