-
View with values duplicates....
Hi,
I've this view
CREATE OR REPLACE VIEW MY_TYPE (UE,COD_ID,R_TYPE,AREA)
AS
select substr(LS.LS_ID, 1, 2) ue,
substr(LS.LS_ID, 3) COD_ID,
BL.R_TYPE,
ROUND(nvl(SUM(nvl(RM.AREA,0)),0),8) AREA
FROM RM, LS, BL
WHERE LS.LS_ID=RM.LS_ID
AND RM.BL_ID=BL.BL_ID
GROUP BY LS.LS_ID,BL.R_TYPE
Code:
UE COD_ID R_TYPE AREA
01 307370 INDUSTRIALI 67.67
01 307370 UFFICI 15806
05 307423 INDUSTRIALI 802.76
05 307423 PROMISCUI 3598.116417
05 307423 UFFICI 671.09
10 007740 PROMISCUI 1545.11
10 007740 UFFICI 403
10 604900 ALTRO 65.04
10 604900 PROMISCUI 1014.014844
20 502260 N/A 95.18464993
20 502260 PROMISCUI 42281.879291
EM S00014 PROMISCUI 26841.32436954
EM S00014 PROMISCUI 8325.99915746
EM S00015 N/A 19123.86215151
EM S00015 UFFICI 8541.54615149
EM S00023 UFFICI 3365.28030655
EM S00023 UFFICI 19107.67106545
- se i valori presenti sono N/A ed un unico "valore significativo" ==> valore significativo
IF RM_TYPE = N/A and only an other value then R_TYPE='OTHER VALUE'
for example UE||COD_ID = EMS00015 R_TYPE='UFFICI'
UE||COD_ID = 20502260 R_TYPE='PROMISCUI'
- se i valori significativi sono più di uno ==> N/A
IF R_TYPE has more different values then R_TYPE='N/A'
for example UE||COD_ID = 05307423 R_TYPE='N/A'
UE||COD_ID = 10007740 R_TYPE='N/A'
therefore I'd like to get these values:
Code:
UE COD_ID R_TYPE AREA
01 307370 N/A 67.67
01 307370 N/A 15806
05 307423 N/A 802.76
05 307423 N/A 3598.116417
05 307423 N/A 671.09
10 007740 N/A 1545.11
10 007740 N/A 403
10 604900 N/A 65.04
10 604900 N/A 1014.014844
20 502260 PROMISCUI 95.18464993
20 502260 PROMISCUI 42281.879291
EM S00014 PROMISCUI 26841.32436954
EM S00014 PROMISCUI 8325.99915746
EM S00015 UFFICI 19123.86215151
EM S00015 UFFICI 8541.54615149
EM S00023 UFFICI 3365.28030655
EM S00023 UFFICI 19107.67106545
How can I get these values from my query?
CASE WHEN clause?
Thanks in advance!
-
- se i valori significativi sono più di uno ==> N/A
IS IT RUSSIAN/FRENCH/GERMAN?
Tamil
-
Italian:
se i valori significativi sono più di uno ==> N/A
If more that one significant value then the answer is N/A. (I guess N/A original values are not significant.)
Last edited by DaPi; 12-03-2004 at 03:36 PM.
-
The OS disk on my home server has just died - boo hoo! So I can't experiment, so this will need hacking . . .
The following will identify the rows that are not N/A:
Code:
Select LS_ID, Max(RTYPE) MyType
From whatever
Where RTYPE <> 'N/A'
Group by LS_ID
Having count(DISTINCT RTYPE) = 1
You could outer join that sub-query (is that legal? *** - might need a work-around) to the original query, using NVL(MyType, 'N/A') to get the final value.
It looked as if an analytic function was the answer, but I can't see it.
*** Just seen examples of this on AskTom so its OK. Must have been thinking of Rdb or SQL/400 or something . . . ,
Last edited by DaPi; 12-04-2004 at 03:38 PM.
-
I tried this
create or replace view my_type as
select substr(ls.ls_id, 1, 2) ue,
substr(ls.ls_id, 3) cod_id,
round(nvl(sum(rm.area),0),8) area,
max(decode(min(distinct decode(bl.r_type,
'N/A',null,
bl.r_type)),
max(distinct decode(bl.r_type,
'N/A',null,
bl.r_type)),
min(distinct decode(bl.r_type,
'N/A',null,
bl.r_type)),
'N/A'))
over (partition by ls.ls_id) r_type
from rm,
ls,
bl
where ls.ls_id = rm.ls_id
and rm.bl_id = bl.bl_id
group by ls.ls_id,
bl.r_type
Have you any idea??
Thanks
-
If that works, it's too clever for me.
How about something like:
Code:
create or replace view my_type as
select ue, code_id, area,
CASE WHEN max_rtype = min_rtype THEN max_rtype ELSE 'N/A' END rtype
from
(select substr(ls.ls_id, 1, 2) ue,
substr(ls.ls_id, 3) cod_id,
round(nvl(sum(rm.area),0),8) area,
max(decode(bl.r_type,'N/A',null,bl.r_type)) over (partition by ls.ls_id) max_rtype,
min(decode(bl.r_type,'N/A',null,bl.r_type)) over (partition by ls.ls_id) min_rtype
from rm,
ls,
bl
where ls.ls_id = rm.ls_id
and rm.bl_id = bl.bl_id)
OOOPS: area needs an OVER clause too.
Last edited by DaPi; 12-06-2004 at 12:03 PM.
-
I tried with these data:
CREATE TABLE TAB_TEST2 (
UE VARCHAR2 (2),
COD_ID VARCHAR2 (30),
TIPO_EDIFICIO CHAR (1),
R_TYPE VARCHAR2 (20),
CLIENTE NUMBER,
ANALITICA VARCHAR2 (64),
AREA NUMBER)
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'ALTRO', 10116, 'ARCHIVI/DEPOSITI', 2.9339897);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'ALTRO', 10116, 'POSTO TELEFONICO PUBBLICO/CTA', 62.1060103);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10027, 'LOCAL', 13.02123361);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10116, 'ARCHIVI/DEPOSITI', 22.24162096);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10116, 'LOC'
, 608.70682006);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10116, 'LOCAL', 243.77675196);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10116, 'MAGAZ'
, 61.80690343);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'10', '604900', 'T', 'PROM', 10116, 'UFF TRADIZIONALI/OPEN SPACE', 54.08180508);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '040683', 'S', 'PROM', 10116, 'LOC'
, 124.819533);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '040683', 'S', 'UFF', 10000, 'ALTRI SPAZI DI SUPPORTO DESTINABILI AD USO UFFO'
, 202.29487);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'PROM', 10027, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 12.33221701);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'PROM', 10116, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 109.78179195);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'PROM', 10116, 'ALTRI SPAZI DI SUPPORTO DESTINABILI AD USO UFFO'
, 63.11006276);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'PROM', 10116, 'LOC'
, 134.55659725);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'PROM', 10116, 'LOCAL', 10.49778299);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'12', '602094', 'S', 'UFF', 10116, 'ALTRI SPAZI DI SUPPORTO DESTINABILI AD USO UFFO'
, 78);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'PROM', 10027, 'LOCAL', 14.99824504);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'PROM', 10058, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 45.14719504);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'PROM', 10116, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 99.23850925);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'PROM', 10116, 'LOCAL', 276.19689828);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'PROM', 10116, 'SERVIZI IGIENICI', 5.44383242);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'16', '862938', 'S', 'UFF', 10017, 'ALTRI SPAZI DI SUPPORTO DESTINABILI AD USO UFFO'
, 122.2725);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'N/A', 10027, 'LOCALI TECNOLOGICI DI EDIFICIO DEDICATI', 1.86485782);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'N/A', 10032, 'ALLOGGI', 2.84535007);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'N/A', 10048, 'UFF TRADIZIONALI/OPEN SPACE', 25.43512257);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'N/A', 10116, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 65.03931947);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10062, 'LOC'
, 26.57693919);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'ALTRI SPAZI DI SERVIZIO NON DESTINABILI AD UFFO'
, 147.6424432);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'ALTRI SPAZI DI SUPPORTO DESTINABILI AD USO UFFO'
, 931.87760945);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'ARCHIVI/DEPOSITI', 308.1971425);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'ARCHIVI/DEPOSITI DI PIANO', 223.2280182);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'LOC'
, 2864.26128793);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'LOCALI APPARATI PER TLC - PONTE RADIO', 56.9870149);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'LOCAL', 3139.13979182);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'MAGAZ'
, 2602.14030055);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SALA DATI (AD ES. ISDN, ITAPAC)', 713.9706888);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SALE D`ATTESA', 106.84742039);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SALE RIPOSO/SPOGLIATOI', 108.84771292);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SALE RIUNIONI', 328.21438345);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SALE RIUNIONI/CONFERENZE', 333.24311549);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'SHOWROOM', 55.92614197);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'UFF CENTRI DI LAVORO', 203.9497474);
INSERT INTO TAB_TEST2 ( UE, COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE, ANALITICA,
AREA ) VALUES (
'20', '502260', 'T', 'PROM', 10116, 'UFF TRADIZIONALI/OPEN SPACE', 19748.53163768);
CREATE OR REPLACE VIEW MY_TEST ( UE,
COD_ID, TIPO_EDIFICIO, R_TYPE, CLIENTE,
ANALITICA, AREA ) AS select UE, COD_ID, TIPO_EDIFICIO, DECODE(R_TYPE, 'N/A', R_TYPE2, R_TYPE1) AS R_TYPE, CLIENTE, ANALITICA,AREA FROM (select UE, COD_ID,TIPO_EDIFICIO, R_TYPE,CLIENTE, ANALITICA, AREA, case when (select count(*) from tab_test2 a
where a.ue = b.ue
and a.cod_id = b.cod_id
and a.R_TYPE <> 'N/A'
and a.R_TYPE <> b.R_TYPE) > 0 THEN 'N/A' ELSE b.R_TYPE end R_TYPE1,
DECODE(R_TYPE, 'N/A', lead(R_TYPE, 1) over (order by UE, COD_ID, DECODE(R_TYPE, 'N/A', 1, 2)), R_TYPE) R_TYPE2
from tab_test2 b)
but if I run:
SELECT UE, COD_ID, R_TYPE
FROM MY_TEST
GROUP BY UE, COD_ID, R_TYPE
I get these values:
UE........COD_ID.........R_TYPE
10........604900.........N/A
12........040683.........N/A
12........602094.........N/A
16........862938.........N/A
20........502260.........N/A
20........502260.........PROM
20502260 must have only 'PROM'
WHAT I WRONG?
THANKS!
-
What's wrong? - Something to do with lead.
I think this does what you want:
Code:
select ue, cod_id,
CASE WHEN max_rtype = min_rtype THEN max_rtype ELSE 'N/A' END rtype,
area
from
(select ue, cod_id, area,
max(decode(r_type,'N/A',null,r_type)) over (partition by ue, cod_id) max_rtype,
min(decode(r_type,'N/A',null,r_type)) over (partition by ue, cod_id) min_rtype
from TAB_TEST2)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|