Results 1 to 3 of 3

Thread: problem on view

  1. #1
    Join Date
    Jul 2002
    Posts
    227

    Unanswered: problem on view

    I've this problem:

    I have a view which has the following col:
    cod_imm
    A000-0-23232323
    A000-0-23232323

    B100-0-12121212
    B100-0-12121212

    C201-0-13131313
    C312-0-14141414

    I would now like that when there are duplicate codes the X (A000-X-23232323) is put, while where the code is not duplicate zero is left.

    A000-0-23232323
    A000-X-23232323

    B100-0-12121212
    B100-X-12121212

    C201-0-13131313
    C312-0-14141414
    My attual code view is this:
    CREATE OR REPLACE VIEW code_view ( SITO,
    TIPO_OGG, COD_IMM, PT_SERVCO, CT_SERVCO,
    DENOM_AZ, NOTE, COD_IMM_SUP, NOP,
    INDIRIZZO, CAP, CODE_PROV, COMUNE,
    COUNTRY, TEL_CENTR, FAX_CENTR, PIANI_ET,
    SUP_COPERTA, PIANI_FT, DATA_CESSAZ, DATA_AGGIOR_AFM,
    CODE_CT_UBIC, CODE_CT_COMP, DATA_FINE_UTILIZZO, COD_IMM_PRECEDENTE,
    COD_IMM_SUCCESSIVO, PROPRIETA ) AS
    select
    AFM_COD_CONV.SITE_ID,
    'A',
    AFM_COD_CONV.COD_CONV||'-0-'||AFM_COD_CONV.SITE_ID,'','',
    SITE.NAME DENOM_AZ,'','',
    'EMS',
    SITE.ADDRESS1 INDIRIZZO,
    SITE.ZIP CAP,
    SITE.COUNTY_ID COD_PROV,
    SITE.CITY_ID COMUNE,
    SITE.CTRY_ID COUNTRY,'','','','','','',
    '01012003','','','','','',''
    FROM AFM_COD_CONV, SITE, aree_verdi
    WHERE AFM_COD_CONV.SITE_ID=SITE.SITE_ID
    and SITE.SITE_ID=aree_verdi.cod_imm
    GROUP BY 'AREE_VERDI',
    AFM_COD_CONV.COD_CONV,
    AFM_COD_CONV.SITE_ID,
    SITE.NAME,
    SITE.ADDRESS1,
    SITE.ZIP,
    SITE.COUNTY_ID,
    SITE.CITY_ID,
    SITE.CTRY_ID)

    May be with decode on AFM_COD_CONV.COD_CONV||'-0-'||AFM_COD_CONV.SITE_ID??
    Could you help me???

    Thanks
    Raf

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: problem on view

    You could certainly use a DECODE or CASE once you know whether the row is a duplicate or not:

    AFM_COD_CONV.COD_CONV
    ||DECODE(is_duplicate,'Y','-X-','-0-')
    ||AFM_COD_CONV.SITE_ID

    or

    AFM_COD_CONV.COD_CONV
    ||CASE WHEN is_duplicate='Y' THEN '-X-' ELSE '-0-' END
    ||AFM_COD_CONV.SITE_ID

    The next problem is identifying which records are "duplicates".

  3. #3
    Join Date
    Jul 2002
    Posts
    227
    Andrewst my full view is this:

    CREATE OR REPLACE VIEW code_view( SITO,
    TIPO_OGG, COD_IMM, PT_SERVCO, CT_SERVCO,
    DENOM_AZ, NOTE, COD_IMM_SUP, NOP,
    INDIRIZZO, CAP, CODE_PROV, COMUNE,
    COUNTRY, TEL_CENTR, FAX_CENTR, PIANI_ET,
    SUP_COPERTA, PIANI_FT, DATA_CESSAZ, DATA_AGGIOR_AFM,
    CODE_CT_UBIC, CODE_CT_COMP, DATA_FINE_UTILIZZO, COD_IMM_PRECEDENTE,
    COD_IMM_SUCCESSIVO, PROPRIETA ) AS (
    SELECT SITE.SITE_ID, DECODE(LS.USE1,'21','EDIFICIO','22','EDIFICIO','23 ','EDIFICIO','24',
    'EDIFICIO','25','EDIFICIO','26','EDIFICIO','31','E DIFICIO','41','EDIFICIO',
    '42','EDIFICIO','51','EDIFICIO','52','EDIFICIO','5 4','EDIFICIO','57','EDIFICIO',
    '58','EDIFICIO','64','EDIFICIO','60','EDIFICIO','6 3','CONTAINER','65','CONTAINER',
    '62','PALO','66','RIPETITORE','61','TRALICCIO','67 ','TRALICCIO','68','TRALICCIO',
    '1','TERRENO','2','TERRENO','3','TERRENO','4','TER RENO','5','TERRENO','6','TERRENO','N/A') TIPO_OGG,
    COD_CONV_IMM.COD_IMM,
    NOP.PT PT_SERVCO,
    --DECODE(NOP,'N/A',NULL,NULL,NULL,SUBSTR(NOP,1,2)) PT_SERVCO,
    CT_SERVCO,
    SITE.NAME DENOM_AZ,
    NOTE,
    decode(CODE_BL_PADRE,null,null,substr(CODE_BL_PADR E,1,2)||'-'||TO_CHAR(TO_NUMBER(SUBSTR(CODE_BL_PADRE,3)))) COD_IMM_SUP,
    SITE.NOP,
    SITE.ADDRESS1 INDIRIZZO,
    SITE.ZIP CAP,
    SITE.COUNTY_ID CODE_PROV,
    SITE.CITY_ID COMUNE,
    SITE.CTRY_ID COUNTRY,
    TEL_CENTR,
    FAX_CENTR,
    null PIANI_ET,
    null SUP_COPERTA,
    null PIANI_FT,
    TO_CHAR(DATA_CESSAZ,'DDMMYYYY') DATA_CESSAZ,
    TO_CHAR(DATA_AGGIOR_AFM,'DDMMYYYY') DATA_AGGIOR_AFM,
    CODE_CT_UBIC,
    CODE_CT_COMP,
    TO_CHAR(DATA_FINE_UTILIZZO,'DDMMYYYY') DATA_FINE_UTILIZZO,
    COD_IMM_PRECEDENTE,
    COD_IMM_SUCCESSIVO,
    LS.PROPRIETA
    FROM LS,NOP,COD_CONV_IMM, SITE, RM, DP
    WHERE /*FLAG_CANC='0'
    AND*/ SITE.NOP = NOP.NOP(+)
    AND RM.LS_ID=COD_CONV_IMM.LS_ID
    AND SITE.SITE_ID=COD_CONV_IMM.SITE_ID
    AND LS.LS_ID=RM.LS_ID
    AND SITE.SITE_ID=RM.SITE_ID
    AND RM.DV_ID=DP.DV_ID
    AND RM.DP_ID=DP.DP_ID
    AND (DP.FLAG_GR='2' OR COD_CONV_IMM.AMMINISTRATORE='EMSA')
    GROUP BY SITE.SITE_ID, LS.USE1, COD_CONV_IMM.COD_IMM, NOP.PT, CT_SERVCO,
    SITE.NAME,
    NOTE,
    decode(CODE_BL_PADRE,null,null,substr(CODE_BL_PADR E,1,2)||'-'||TO_CHAR(TO_NUMBER(SUBSTR(CODE_BL_PADRE,3)))),
    SITE.NOP,
    SITE.ADDRESS1 ,
    SITE.ZIP ,
    SITE.COUNTY_ID,
    SITE.CITY_ID ,
    SITE.CTRY_ID ,
    TEL_CENTR,
    FAX_CENTR,
    DATA_CESSAZ,
    DATA_AGGIOR_AFM,
    CODE_CT_UBIC,
    CODE_CT_COMP,
    DATA_FINE_UTILIZZO,
    COD_IMM_PRECEDENTE,
    COD_IMM_SUCCESSIVO,
    LS.PROPRIETA
    )
    UNION
    (select
    AFM_COD_CONV.SITE_ID,
    'AREE VERDI',
    AFM_COD_CONV.COD_CONV||'-0-'||AFM_COD_CONV.SITE_ID,
    '',
    '',
    SITE.NAME DENOM_AZ,
    '',
    '',
    'EMS',
    SITE.ADDRESS1 INDIRIZZO,
    SITE.ZIP CAP,
    SITE.COUNTY_ID COD_PROV,
    SITE.CITY_ID COMUNE,
    SITE.CTRY_ID COUNTRY,'','','','','','','01012003','','','','',' ',''
    FROM AFM_COD_CONV, SITE, aree_verdi
    WHERE AFM_COD_CONV.SITE_ID=SITE.SITE_ID
    and SITE.SITE_ID=aree_verdi.cod_imm
    GROUP BY 'AREE_VERDI',
    AFM_COD_CONV.COD_CONV,
    AFM_COD_CONV.SITE_ID,
    SITE.NAME,
    SITE.ADDRESS1,
    SITE.ZIP,
    SITE.COUNTY_ID,
    SITE.CITY_ID,
    SITE.CTRY_ID)

    Now in first select there is a code A000-0-23232323 with tipo_ogg='A', while in the second (union) view there is code A000-0-23232323
    with tipo_ogg='B', in this case I must insert X (A000-X-23232323).

    Thanks for your help!
    Raf

Posting Permissions

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