-
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
-
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','EDIFICIO','41','EDIFICIO',
'42','EDIFICIO','51','EDIFICIO','52','EDIFICIO','54','EDIFICIO','57','EDIFICIO',
'58','EDIFICIO','64','EDIFICIO','60','EDIFICIO','63','CONTAINER','65','CONTAINER',
'62','PALO','66','RIPETITORE','61','TRALICCIO','67','TRALICCIO','68','TRALICCIO',
'1','TERRENO','2','TERRENO','3','TERRENO','4','TERRENO','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_PADRE,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_PADRE,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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|