Dirty SQL Queries of our time

bonkey

The following is a simplification of somerthing I've been working on for a bit today, and I thought it might be interesting to post up.

I'm also hoping that someone may spot a neater/more efficient way of doing what it is I need to do...cause this approach is ugly IMHO. Or you might spot a bug/problem. I think its right tho
So....

I've got a table customerRatings which contains the following information :

customerID
ratingAgency
ratingDate
ratingType
ratingValue

customerID is, incidentally, not unique here. In fact, the Primary key is the first 4 of the 5 fields.

What I need to do is create a View which encapsulates the following :

Desired Output : customerId, ratingValue

There will be one row per customerId with the ratingValue calculated as follows

- If the customer has rating(s) by Agency 'A', take the rating with the highest value
- otherwise, find the most recent rating of Type X for the customer and take that.
- if more than one rating is found in the previous step, take the one with
the highest value
- if none of these conditions are met, return NULL as the rating for this customer


So how do we do it?

I should mention that I need to be compatible with both Oracle 8i, so I can't use ANSI join-syntax, nor can I use CASE statements.

Here's what I've come up with

[php]
SELECT a.customerId
, nvl( max( decode( trim( a.ratingAgency )
, 'A', a.ratingValue
, null
)
)
, max( decode( trim( a.ratingAgency )
, 'A', null
, decode( a.ratingDate
, a1.maxRatingDate, a.ratingValue
, null
)
)
)
) as finalRating
FROM customerRating a
, ( SELECT a1_in.customerId
, max(ratingDate) as maxRatingDate
FROM customerRating a1_in
WHERE trim(a1_in.ratingAgency) <> 'A'
AND a1_in.ratingType = 'X'
GROUP BY a1_in.customerId
) a1
WHERE a.customerRating = a1.customerRating (+)
AND a.ratingDate = a1.maxRatingDate (+)
GROUP BY
a.customerId
[/php]
So? Comments?

(Told ya it was ugly)

<edit>
FFS...how fscknig hard can it be to get this thing to format properly.
</edit>

bonkey

Its just occurred to me that I can probably use Oracle's analytical functions to get the max date without needing the sub-query....

Hmmm....

bonkey

Nah. Don't think I can use analyticals to simplify.

Civilian_Target

It's not that ugly.
I've seen statements that are 4 A4 printed pages long and take almost a second of CPU time to execute being run on production systems.
Given what you're describing your statement makes sense, if it works...

rsynnott

No. Silly. That's what the middle layer is for.

Zaph0d

bonkey wrote:

- If the customer has rating(s) by Agency 'A', take the rating with the highest value
- otherwise, find the most recent rating of Type X for the customer and take that.
- if more than one rating is found in the previous step, take the one with
the highest value
- if none of these conditions are met, return NULL as the rating for this customer

One thing I noticed when implementing detailed business rules like this is that often the meaning was too complex to be understood by end users. So the system would be correct but incomprehensible. If I suspect this is happening, I mock up the output and ask a few end users to explain what it means. If they can't, I try to get the client to simplify the rules, or maybe present the data in a different way.

One time I had to write code to calculate salaries for salesmen based on weekly results. The rules took a page to explain. Because no-one understood them (except the boss and me), there was no good link between pay and performance. Every month the salaries might as well have been random numbers.

Of course this is not always true and possibly the above rules cannot be simplified or divided into separate queries.

Where performance is not a factor, I would aim for readability with a query like this. I'd try to write one query for each rule and aggregate them. That way a rule can be turned off or a new one added.