using kind of alias

#1

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.

#2

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#3

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#4

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#5

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#6

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#7

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#8

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...

Quote:

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.

By redesign. This is horrible.

Shakespeare

#9

Shakespeare <whatsin (AT) xs4all (DOT) nl> wrote:

Quote:

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...
Can someone please help me on the following.

<snip>

Quote:

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

Quote:

By redesign. This is horrible.

Shakespeare

I'm inclined to agree with Shakespeare. However, if you MUST use this
bizarre data model, there are a number of ways to rewrite your query.
Here is a simple one:

select decode(cnt, 0, fam1, 1111) f1, ...
from (select u.item, u.fam1, u.fam2, u.fam3, u.fam4,
(select count(*)
from table1 t
where t.item = u.item
and t.fam1 != u.fam1) cnt
from table1 u);

There is probably a more efficient way to do it, but I am disinclined to
spend more time thinking about it.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."

#10

Shakespeare <whatsin (AT) xs4all (DOT) nl> wrote:

Quote:

"Ronald" <rmoleveld (AT) gmail (DOT) com> schreef in bericht
news:22044960-3e83-4a47-8a40-3358c763cc5e (AT) t54g2000hsg (DOT) googlegroups.com...
Can someone please help me on the following.

<snip>

Quote:

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

Quote:

By redesign. This is horrible.

Shakespeare

I'm inclined to agree with Shakespeare. However, if you MUST use this
bizarre data model, there are a number of ways to rewrite your query.
Here is a simple one:

select decode(cnt, 0, fam1, 1111) f1, ...
from (select u.item, u.fam1, u.fam2, u.fam3, u.fam4,
(select count(*)
from table1 t
where t.item = u.item
and t.fam1 != u.fam1) cnt
from table1 u);

There is probably a more efficient way to do it, but I am disinclined to
spend more time thinking about it.

--
__________________________________________________ _____________________
Dan Blum tool (AT) panix (DOT) com
"I wouldn't have believed it myself if I hadn't just made it up."