Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > case statement - strange results
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 10th December 03:51
stephen od
External User
 
Posts: 1
Default case statement - strange results


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.
  Reply With Quote


  sponsored links


2 10th December 03:58
da morgan
External User
 
Posts: 1
Default case statement - strange results


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@x.washington.edu
(replace x with u to respond)
  Reply With Quote
3 10th December 04:00
barbara boehmer
External User
 
Posts: 1
Default case statement - strange results


scott@ORA92> -- test table and data:
scott@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@ORA92> column account_num format a11
scott@ORA92> column event_seq format 999999999
scott@ORA92> column attr_2 format a15
scott@ORA92> column attr_11 format a15
scott@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@ORA92> -- reproduction of error:
scott@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@ORA92> -- correction:
scott@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@ORA92>
  Reply With Quote
4 10th December 04:08
stephen od
External User
 
Posts: 1
Default case statement - strange results


In response to Daniel -

I am fully aware of normalization and using a date to store times. I
did state that this is a vendor application that am an attempting to
product reports on, and have no control over the database structure.
What it comes down to here, is that the application needs to store many
different event types (event_type_id's) in this table (the real table
has infact 36 attr_ columns including some other data). Each of these
event types can have 1 - 36 attributes, with different types and names.
The events are then rated depending on the attributes supplied - for
my event 55, its cost depends on the duration, time of date and various
other factors. For event 56, it depends on the value in attr_2 only
etc. I ask how you would construct such a configurable system without
using a kludge?

That aside, the point here is whether Oracle is working correctly or
not, not how much I know about database design.

Barbara - Of course! I was using the case and decode differently. It
was like I was doing:-

decode (event_type_id, 55, sum(), 56 sum(), sum(1) ).

I can assure you I won't make that mistake again.

Thanks for your help!
  Reply With Quote
5 10th December 04:57
da morgan
External User
 
Posts: 1
Default case statement - strange results


I missed that part. Please name the vendor so that others will not
purchase their product. There is no excuse for building something
this ugly and hopefully others, forwarned, will be able to convince
their management to find a better product.

Thanks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666