DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: View with values duplicates....

  1. #1
    Join Date
    Jul 2002
    Posts
    228

    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!

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    - se i valori significativi sono più di uno ==> N/A

    IS IT RUSSIAN/FRENCH/GERMAN?

    Tamil

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Angry

    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.

  5. #5
    Join Date
    Jul 2002
    Posts
    228
    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

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  7. #7
    Join Date
    Jul 2002
    Posts
    228
    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!

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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
  •  


Click Here to Expand Forum to Full Width