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.)

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 . . . ,

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.

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)