using kind of alias in sqlplus

hi all.

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.

"Ronald" wrote in message

> hi all.
>
> 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.

Hi Ronald

This is a SQL Server newsgroup, so knowledge of Oracle is limited!

Its not clear to me exactly you are trying to do posting DDL and example
data with the expected output would help considerably

I think your last example is probably near to what you want, hopefully you
can do this in oracle.


select t.item item,
COUNT ( DISTINCT t.fam1) fam1,
COUNT ( DISTINCT t.fam2) fam2,
COUNT ( DISTINCT t.fam3) fam3
COUNT ( DISTINCT t.fam4) fam4
from table1 t
group by t.item

Will tell you if for each item the number of distinct values for the columns
are unique.


SELECT u.item,
DECODE(cnt.fam1,1,t.fam1,1111) F1,
DECODE(cnt.fam2,1,t.fam2,2222) F2,
DECODE(cnt.fam3,1,t.fam3,3333) F3,
DECODE(cnt.fam4,1,t.fam4,4444) F4
from table1 u,
( select t.item item,
COUNT ( DISTINCT t.fam1) fam1,
COUNT ( DISTINCT t.fam2) fam2,
COUNT ( DISTINCT t.fam3) fam3
COUNT ( DISTINCT t.fam4) fam4
from table1 t
group by t.item ) cnt
WHERE u.item = cnt.item


HTH

John