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:-

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:-

Can anyone else reproduce this error on 9i (or better get it work), or
am I doing something very stupid?

Thanks,

Stephen.

Da morgan 2006-12-10 03:58:32

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. Morganhttp://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

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!

Da morgan 2006-12-10 04:57:03

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.