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