Data quality ranking problem

From: Soren Kuula <dongfang_at_dongfang.dk>
Date: Thu, 14 Sep 2006 09:23:05 +0200
Message-ID: <45090386$1_3@news.bluewin.ch>

Hi,

I have to implement a simple data quality ranking system.

Say, we have a relation A

ID NUMBER PRIMARY KEY,
FOO NUMBER,
BAR VARCHAR2 (in my real project, A has many more (too many) columns...)

I am given a ranking table (data view here): COL_NAME RANK

---

'FOO' 10
'BAR' 15

• and now I have to implement:

For some given row in A, compute a score. Start with 0. If the FOO field is not NULL, add 10. If the BAR field is not NULL, add 15, etc ....

Does anyone know a solution? It resembles a little of reflection -- find a field by its name, but the name is not known statically. Of course I could make a huge PL/SQL thing with IF or CASE, but there may be a more elegant solution?

Part 2:

Add a relation B

ID NUMBER PRIMARY KEY,
KEY_A NUMBER, -- foreign key to A
FOOBAR NUMBER
FOOBAz NUMBER

I also have ranks for columns in B that have a key to the row in A that I want to rank:

COL_NAME RANK

---

'FOOBAR' 20
'FOOBAR' 25
Here the ranking rule is: Of the columns in B with that have a key to our row in A, find the one with the highest score (or just one with the fewest number of NULL fields; approximation allowed). Compute its rank, and add that to the rank of the A row.

I am not afraid of having to solve it myself, but if someone knows the situation and a solution, I'd be happy to hear about it.

Soren Received on Thu Sep 14 2006 - 02:23:05 CDT