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