Re: need query of wildly unormalized table

On Thu, 11 Oct 2007 08:46:11 -0700, EdStevens <quetico_man_at_yahoo.com> wrote:

>Platform: Oracle 10.2.0.1.0
>
>Given a table with a structure something like this
>
>key_field varchar2(10)
>award_1 varchar2(4)
>award_2 varchar2(4)
>award_3 varchar2(4)
> <snip repeatage>
>award_30 varchar2(4)
>
>The requirement is to report all 'key_field' where any of the
>'award_*' columns contain a selected value. The crude way is
>
>SELECT key_field
>FROM .....
>WHERE
> award_1 = 'AAAA' or
> award_2 = 'AAAA' or
> award_3 = 'AAAA' or
> <snip repeatage>
> award_30 = 'AAAA' or

This would be better as an IN clause. Both cleaner, and makes the variable in one place.

SELECT key_field
FROM .....
WHERE
  'AAAA' IN (award_1, award_2, award_3, ...award_30 = 'AAAA');

>Surely there is a more elegant way of dealing with this mess,

Well, if you mean query-wise, no, there isn't. That query fits the requirements exactly. What can be more elegant that that?

If you mean a better way of dealing with the situation, perhaps you could CREATE a VIEW that puts each award COLUMN separately. Or, CREATE a new TABLE, and put TRIGGERs on the first TABLE to keep the second TABLE up to date. (Or, replace the first TABLE, make a correct TABLE in its stead, and replace the old TABLE with a VIEW and an INSTEAD OF TRIGGER). B. Received on Fri Oct 12 2007 - 08:20:25 CDT