Re: using kind of alias

"Neil.W.James" <news_at_NONSPAM.familyjames.com> schreef in bericht news:48553aff$0$905$ba4acef3_at_news.orange.fr...
> Ronald wrote:
>> Can someone please help me on the following.
>>
>> Example query:
>>
>> Tabel1 contains a lot of items.
>>
>> TABLE1
>> ITEM VARCHAR2(30),
>> FAM1 VARCHAR2(10),
>> FAM2 VARCHAR2(10),
>> FAM3 VARCHAR2(10),
>> FAM4 VARCHAR2(10)
>>
>> Table contains a lot of records with the same itemnumber.
>> Every item has a family combination from fam1 until fam4
>>
>> I need a list of items and all fam's with the following rules:
>> if all FAM1 are equal for that item take FAM1 otherwise get default
>> value 1111
>> If all FAM1 are equal for that item take FAM2 otherwise get default
>> value 2222
>> If all FAM1 are equal for that item take FAM3 otherwise get default
>> value 3333
>> If all FAM1 are equal for that item take FAM4 otherwise get default
>> value 4444
>>
> [Snip]
>> Any idea/help how to solve this?
>>
>> Thanks in advance.
>>
>> Ronald.
>
> Try Analytics, something like
>
> SELECT item,
> DECODE(min1, max1, min1, 1111) fam1,
> DECODE(min1, max1, fam2, 2222) fam2,
> DECODE(min1, max1, fam3, 3333) fam3,
> DECODE(min1, max1, fam4, 4444) fam4
> FROM (SELECT item,
> MIN(fam1) OVER (PARTITION BY item
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min1,
> MAX(fam1) OVER (PARTITION BY item
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max1,
> fam2,
> fam3,
> fam4
> FROM table);
>
> Regards,
> Neil

And hope you'll never have to perform any maintenance for this system.....

Shakespeare Received on Tue Jun 17 2008 - 14:39:02 CEST