Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: Oracle 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			IND	  67.67
    01	307370			UFF		  15806
    
    05	307423			IND	  802.76
    05	307423			PROM	  3598.116417
    05	307423			UFF		  671.09
    
    10	007740			PROM	  1545.11
    10	007740			UFF		  403
    
    10	604900			ALTRO		  65.04
    10	604900			PROM	  1014.014844
    
    20	502260			N/A		  95.18464993
    20	502260			PROM	  42281.879291
    
    EM	S00014			PROM	  26841.32436954
    EM	S00014			PROM	  8325.99915746
    
    EM	S00015			N/A		  19123.86215151
    EM	S00015			UFF		  8541.54615149
    
    EM	S00023			UFF	  	  3365.28030655
    EM	S00023			UFF	  	  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='UFF'
    UE||COD_ID = 20502260 R_TYPE='PROM'

    - 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			PROM	  95.18464993
    20	502260			PROM	  42281.879291
    
    EM	S00014			PROM	  26841.32436954
    EM	S00014			PROM	  8325.99915746
    
    EM	S00015			UFF		  19123.86215151
    EM	S00015			UFF		  8541.54615149
    
    EM	S00023			UFF	  	  3365.28030655
    EM	S00023			UFF	  	  19107.67106545
    How can I get these values from my query?
    CASE WHEN clause?

    Thanks in advance!

  2. #2
    Join Date
    Jul 2002
    Posts
    227
    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:

    Code:
    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!

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Code:
    SQL> select ue, cod_id, r_type, area
      2    from tab_test2;
    
    UE COD_ID     R_TYPE                AREA
    -- ---------- --------------- ----------
    01 307370     IND                  67.67
    01 307370     UFF                  15806
    05 307423     IND                 802.76
    05 307423     PROM            3598.11642
    05 307423     UFF                 671.09
    10 007740     PROM               1545.11
    10 007740     UFF                    403
    10 604900     ALTRO                65.04
    10 604900     PROM            1014.01484
    20 502260     N/A             95.1846499
    20 502260     PROM            42281.8793
    EM S00014     PROM            26841.3244
    EM S00014     PROM            8325.99916
    EM S00015     N/A             19123.8622
    EM S00015     UFF             8541.54615
    EM S00023     UFF             3365.28031
    EM S00023     UFF             19107.6711
    
    17 rows selected.
    
    SQL> select ue, cod_id,
      2         case when max( decode( r_type, 'N/A', 'N/A' ) ) over( partition by ue, cod_id ) is null
      3               and max( r_type ) over( partition by ue, cod_id ) <> min( r_type ) over( partition by ue, cod_id )
      4              then 'N/A'
      5              else nvl( lead( r_type ) over( partition by ue, cod_id order by rownum ), r_type )
      6           end r_type
      7    from tab_test2
      8  /
    
    UE COD_ID     R_TYPE
    -- ---------- ---------------
    01 307370     N/A
    01 307370     N/A
    05 307423     N/A
    05 307423     N/A
    05 307423     N/A
    10 007740     N/A
    10 007740     N/A
    10 604900     N/A
    10 604900     N/A
    20 502260     PROM
    20 502260     PROM
    EM S00014     PROM
    EM S00014     PROM
    EM S00015     UFF
    EM S00015     UFF
    EM S00023     UFF
    EM S00023     UFF
    
    17 rows selected.
    
    SQL>
    I used MIN and MAX in order to avoid the cases when they were two of the same r_type one after another, you can use something else ( like lead( ) ) if you can ensure those will not repeat one after another.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •