using kind of alias

From: Ronald <rmoleveld_at_gmail.com>
Date: Thu, 12 Jun 2008 02:02:01 -0700 (PDT)
Message-ID: <22044960-3e83-4a47-8a40-3358c763cc5e_at_t54g2000hsg.googlegroups.com>

[Quoted] [Quoted] Can someone please help me on the following.

[Quoted] 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
[Quoted]                      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

[Quoted] Any idea/help how to solve this?

Thanks in advance.

Ronald. Received on Thu Jun 12 2008 - 11:02:01 CEST