 | |
using kind of alias
comp.databases.oracle.tools comp.databases.oracle.tools
 |
| | Thread Tools | Display Modes |
#21
 
| |||
| |||
|
Re: using kind of alias -
06-15-2008
, 10:53 AM
|
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. |
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
#22
| |||
| |||
|
|
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. |
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
#23
| |||
| |||
|
|
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 |
Shakespeare
#24
| |||
| |||
|
|
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 |
Shakespeare
#25
| |||
| |||
|
|
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 |
Shakespeare
#26
| |||
| |||
|
|
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 |
Shakespeare
#27
| |||
| |||
|
|
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 |
Shakespeare
#28
| |||
| |||
|
|
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 |
Shakespeare
#29
| |||
| |||
|
|
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 |
Shakespeare
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.