Soren Kuula wrote:
> 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
Bulk collect, traverse the array using a FOR loop. Write the result
to a result table.
--
Daniel Morgan
Puget Sound Oracle Users Group
Received on Thu Sep 14 2006 - 23:07:06 CDT
