Re: need query of wildly unormalized table

HMM If this is a new application you should have a different design In my opinion the table should look like this keyfield,awardnr,awardtext

regards SEJ

always top posting
"Brian Tkatch" <N/A> skrev i en meddelelse news:6psug390r9fua2irj892737t4ng8jm278g_at_4ax.com...
> 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 - 11:08:09 CDT