Re: SELECT DENORMALIZED INFO TABLE

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: Fri, 13 Oct 2000 18:53:59 GMT
Message-ID: <87og0otvta.fsf@HSE-MTL-ppp62507.qc.sympatico.ca>

Mario citec Gagné <mariocitec_at_my-deja.com> writes:

> I have these records:
> C1_KEY LANGUAGE DESC
> ------ -------- ----
> XXX FRENCH French Description
> XXX ENGLISH English Description
> YYY FRENCH French Description
>
> My system support also the Spanish Language and Italian language.
>
> I want to produce a flat File like This using SQL:
>
> 'XXX','French Description','English Description','',''
> 'YYY','French Description','','',''
>
> How can I do the select if I know that sometimes I do not always have
> all the record in my table than the system is supporting and I want my
> key to be unique in the flat file ?

one way (untested):

SELECT a.c1_key, a.desc,b.desc,c.desc,d.desc   FROM table_name a,table_name b,table_name c,table_name d

 WHERE a.c1_key = b.c1_key(+) AND a.c1_key(+) = c.c1_key(+) AND a.c1_key = d.c1_key(+)
   AND a.language < b.language (+)
   AND a.language < c.language (+) AND b.language < c.language (+)
   AND a.language < c.language (+) AND b.language < c.language (+) AND c.language < d.language (+)

 GROUP BY a.c1_key, a.desc,b.desc,c.desc,d.desc

another way involves lots of unions instead

-- 
greg

Received on Fri Oct 13 2000 - 13:53:59 CDT