Re: need query of wildly unormalized table

From: Walt <walt_askier_at_SHOESyahoo.com>
Date: Thu, 11 Oct 2007 16:56:19 -0400
Message-ID: <13gt3fjga325n8e@corp.supernews.com>

EdStevens 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
>
> Surely there is a more elegant way of dealing with this mess, but I'm
> having trouble getting my head wrapped around it. Unfortunately,
> normalizing the data (what a concept!) is beyond my control, other
> than the possibility of using a temp table.
>
> Ideas?
>

Make a view

CREATE VIEW my_view AS
SELECT key_field, 1, award_1 from your_awful_table UNION
SELECT key_field, 2, award_2 from your_awful_table UNION

.
.
.

SELECT key_field, 30, award_30;

Then you can query it like a sane person. And you only have to create the view once instead of every writing a bunch of crap every time you want to query the table.

//Walt

//Walt Received on Thu Oct 11 2007 - 15:56:19 CDT