Re: using kind of alias

[Quoted] "Ronald" <rmoleveld_at_gmail.com> schreef in bericht news:22044960-3e83-4a47-8a40-3358c763cc5e_at_t54g2000hsg.googlegroups.com...
> 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
>
> I'm using now the following query: (its just a translation of my query
> in easy example)
>
> select decode((select count(*)
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1),0,t.fam1,1111) F1,
> decode((select count(*)
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1),0,t.fam2,2222) F2,
> decode((select count(*)
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1),0,t.fam3,3333) F3,
> decode((select count(*)
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1),0,t.fam4,4444) F4
> from table1 u
>
> Because it does the count 4 times (for every FAM) it takes long time.
> Is there a way I can use the results of the first count in the 3 last
> decodes?
>
> (bad example) but kind of:
>
> select decode((select count(*) xxxx
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1),0,t.fam1,1111)
> F1,
> decode(&xxxx,0,t.fam2,2222) F2,
> decode(&xxxx,0,t.fam3,3333) F3,
> decode(&xxxx,0,t.fam4,4444) F4
> from table1 u
>
> I've tried this one:
>
> select decode(cnt.xxxx,0,t.fam1,1111) F1,
> decode(cnt.xxxx,0,t.fam2,2222) F2,
> decode(cnt.xxxx,0,t.fam3,3333) F3,
> decode(cnt.xxxx,0,t.fam4,4444) F4
> from table1 u,
> ( select count(*) xxxx
> from table1 t
> where t.item = u.item
> and t.fam1 <> u.fam1) cnt
>
> but the variables u.fam1 and u.item are not passed trough.
> Puting this in the where clause from u.table1 doesn't help either
> because the <> exclude a lot of items
>
> select decode(cnt.xxxx,0,t.fam1,1111) F1,
> decode(cnt.xxxx,0,t.fam2,2222) F2,
> decode(cnt.xxxx,0,t.fam3,3333) F3,
> decode(cnt.xxxx,0,t.fam4,4444) F4
> from table1 u,
> ( select t.item itm,
> t.fam1 fa1,
> count(*) xxxx
> from table1 t
> ) cnt
> where u.item = cnt.itm
> and u.fam1 <> cnt.fa1
>
> Any idea/help how to solve this?
>
> Thanks in advance.
>
> Ronald.

[Quoted] By redesign. This is horrible.

Shakespeare Received on Thu Jun 12 2008 - 12:26:56 CEST