August 23, 2011 at 3:33 am
Hi,
I am creating a new view, this is an exmaple of view:
Select a.nome,null as morada from table1 as a
union
select b.nome, b.morada from table2 as b
union
select c.nome, null as morada from table3 as c
When i try to run the t-sql I receive the following message:
The column morada was specified multiple times for 'View_name'
Can someone help?
Thank you
August 23, 2011 at 3:41 am
That's not a view - it's just a SELECT statement. Please will you post the whole view definition and we should be able to help you.
Thanks
John
August 23, 2011 at 3:50 am
create view test
as
Select a.nome,null as morada from table1 as a
union
select b.nome, b.morada from table2 as b
union
select c.nome, null as morada from table3 as
Here it is
August 23, 2011 at 3:57 am
Works OK for me. Please will you provide table DDL (in the form of CREATE TABLE statements) and some sample data (in the form of INSERT statements) for table1, table2 and table3.
Thanks
John
August 23, 2011 at 4:02 am
Let me give you the real view:
ALTER view [dbo].[V_SMPN_CONTRIBUINTES]
as
select
a.NIF+isnull(filial_number,'') as NIF,a.COD_MET_TRIBUTARIO,a.NOME,a.STATUS,a.COD_PROVINCIA,
a.COD_REP_FISCAL,null as cod_enquadramento,null as Tipo_Empresa,null as dt_constituicao,null as num_dr,null as dt_dr,
null as capital_social,null as perc_cs_publico,null as perc_cs_privado, null as perc_cs_privado,
a.cod_comuna,a.morada,null as nome_toc,null as nif_toc,a.telefone,a.fax,b.bi as BI,
a.COD_MUNICIPIO, a.NIF_ANTIGO as RGC, convert(varchar(10),a.dt_inicio,112) as DT_INICIO,b.COD_CAE CAE_PRINCIPAL
from contribuintes as a
left join singulares as b
on a.nif_antigo = b.nif_antigo
where
a.nif_antigo is not null
and a.cod_rep_fiscal is not null and (a.cod_met_tributario ='tp1' or a.cod_met_tributario ='tp2')
union all
select
a.NIF+isnull(filial_number,'') as NIF ,a.COD_MET_TRIBUTARIO,a.NOME,a.STATUS,a.COD_PROVINCIA,
a.COD_REP_FISCAL,c.cod_enquadramento,
case isnull(a.filial_number,'0')
when '0' then 'Matriz'
else 'Filial'
end
as Tipo_Empresa, convert(varchar(10),c.dt_constituicao,112), c.num_dr,c.dt_dr,
convert(bigint,c.capital_social) capital_social,convert(bigint,c.perc_cs_publico) perc_cs_publico ,convert(bigint,c.perc_cs_privado) perc_cs_privado,
convert(bigint,c.perc_cs_privado) perc_cs_privado,
a.cod_comuna,a.morada,c.nome_toc,c.nif_toc,a.telefone,a.fax,null as BI,
a.COD_MUNICIPIO, a.NIF_ANTIGO as RGC, convert(varchar(10),a.dt_inicio,112) as DT_INICIO, c.COD_CAE CAE_PRINCIPAL
from contribuintes as a
left join empresa as c
on a.nif_antigo = c.nif_antigo
where
a.nif_antigo is not null
and a.cod_rep_fiscal is not null and a.cod_met_tributario ='tp5'
union all
select
a.NIF+isnull(filial_number,'') as NIF,a.COD_MET_TRIBUTARIO,a.NOME,a.STATUS,a.COD_PROVINCIA,
a.COD_REP_FISCAL,null as cod_enquadramento,null as Tipo_Empresa,null as dt_constituicao,null as num_dr,null as dt_dr,
null as capital_social,null as perc_cs_publico,null as perc_cs_privado, null as perc_cs_privado,
a.cod_comuna,a.morada,null as nome_toc,null as nif_toc,a.telefone,a.fax,null as BI,
a.COD_MUNICIPIO, a.NIF_ANTIGO as RGC, convert(varchar(10),a.dt_inicio,112) as DT_INICIO,Null as CAE_PRINCIPAL
from contribuintes as a
where
a.nif_antigo is not null
and a.cod_rep_fiscal is not null and a.cod_met_tributario ='tp7'
Error: The column 'perc_cs_privado' was specified multiple times for 'V_SMPN_CONTRIBUINTES'.
August 23, 2011 at 4:05 am
You have the same column name multiple times:
<snip>
null as perc_cs_privado, null as perc_cs_privado,
<snip>
Change one of the column aliases to a different name.
-- Gianluca Sartori
August 23, 2011 at 4:07 am
Thank you very much for the help.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply