Prev: Can I do this? invoke function with parameter from join table
Next: Assinging A variable value
From: rafaelSC on 14 Jan 2010 11:24
ALTER view [dbo].[vwIE01] as
with tblcte_casoTipo(casoTipoId,origenId,codigoTipoId,codigoId)
as
(
-- data
select a.casoTipoId,b.origenId,c.codigoTipoId,d.codigoId
from SISFACBM.dbo.tblcasoTipo a
cross join SISFACBM.dbo.tblorigen b
cross join SISFACBM.dbo.tblcodigoTipo c
cross join SISFACBM.dbo.tbltarifaCodigo d
where a.casoTipoPadre is null and a.casoTipoId<>'TODO'
-- recursividad
union all
select b.casoTipoId,a.origenId,a.codigoTipoId,a.codigoId
from tblcte_casoTipo a
join SISFACBM.dbo.tblcasoTipo b on b.casoTipoPadre=a.casoTipoId
)

select casoTipoId,origenId,codigoTipoId,codigoId
from tblcte_casoTipo
From: Tom Cooper on 14 Jan 2010 11:30
No. Indexed views cannot have common table expressions (cte's). See

http://msdn.microsoft.com/en-us/library/ms191432%28SQL.90%29.aspx (for SQL
2005)
http://msdn.microsoft.com/en-us/library/ms191432.aspx (for SQL 2008)

Tom

"rafaelSC" <rafaelSC(a)discussions.microsoft.com> wrote in message
news:905E3697-ABCC-4800-8829-3299DE01D2C4(a)microsoft.com...
> ALTER view [dbo].[vwIE01] as
> with tblcte_casoTipo(casoTipoId,origenId,codigoTipoId,codigoId)
> as
> (
> -- data
> select a.casoTipoId,b.origenId,c.codigoTipoId,d.codigoId
> from SISFACBM.dbo.tblcasoTipo a
> cross join SISFACBM.dbo.tblorigen b
> cross join SISFACBM.dbo.tblcodigoTipo c
> cross join SISFACBM.dbo.tbltarifaCodigo d
> where a.casoTipoPadre is null and a.casoTipoId<>'TODO'
> -- recursividad
> union all
> select b.casoTipoId,a.origenId,a.codigoTipoId,a.codigoId
> from tblcte_casoTipo a
> join SISFACBM.dbo.tblcasoTipo b on b.casoTipoPadre=a.casoTipoId
> )
>
> select casoTipoId,origenId,codigoTipoId,codigoId
> from tblcte_casoTipo

From: Plamen Ratchev on 14 Jan 2010 11:35
No, you cannot index a view that uses the UNION operator.

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Can I do this? invoke function with parameter from join table
Next: Assinging A variable value