Re: using kind of alias

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 Received on Sun Jun 15 2008 - 17:53:35 CEST