View X Top

Can I use in a View the clause TOP ?

If I execute the SQL statement out of a view it returns one line, but if execute a view it don't returns a line.

ALTER VIEW V_PESSOAL_FORMACAO AS
SELECT
TP.IDPESSOAL,
T.IDEMP ,
T.DATINICIAL,
T.DATFINAL DATFORMACAO ,
CR.IDLIVROREGVIG IDLIVROREGVIGF,
CR.NUMREGISTRO NUMREGISTROF ,
CR.LETRAREGISTRO LETRAREGISTROF,
CR.ANOREGISTRO ANOREGISTROF ,
CR.UFREGISTRO UFREGISTROF,
CR.DATREGISTRO DATREGISTROF,
CR.IDEMPCOMISSAO IDEMPCOMISSAOF,
CR.PAGREGISTRO PAGREGISTROF,
T.INDREGISTRO,
TP.INDHISTFORMBASICA,
T.IDTURMA ,
T.INDTV
FROM TURMA_PESSOAL TP WITH (NOLOCK)
INNER JOIN TURMA T WITH (NOLOCK) ON TP.IDTURMA=T.IDTURMA
LEFT OUTER JOIN CURSO_REGISTRO CR WITH (NOLOCK) ON TP.IDTURMA=CR.IDTURMA AND
TP.IDPESSOAL=CR.IDPESSOAL AND CR.INDCANCELADO='N'
WHERE T.INDFORMBASICA='S' AND
TP.INDHISTORICO = 'N' AND
TP.INDAPROVADO='S' AND
TP.INDHISTFORMBASICA='N'
UNION ALL
SELECT top 1 VPFM.IDPESSOAL,
VPFM.IDEMP ,
VPFM.DATINICIAL,
VPFM.DATFORMACAO ,
IDLIVROREGVIGF,
NUMREGISTROF ,
LETRAREGISTROF,
ANOREGISTROF ,
UFREGISTROF,
DATREGISTROF,
IDEMPCOMISSAOF,
PAGREGISTROF,
INDREGISTRO,
INDHISTFORMBASICA,
IDTURMA = NULL ,
VPFM.INDTV
FROM V_PESSOAL_FORMACAO_MILITAR VPFM WITH (NOLOCK)

I think when using a view you need to use an order by clause in conjunction with the top keyword to get results, or it might be you need to use the top keyword in a view when using the order by keyword, I would try it out but unfortunately the computer I'm currently on doesn't have sql server or query analyser.

Cheers
Shaun

World Domination Through Superior Software

See

http://www.devx.com/dbzone/Article/8048

HTH.

Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

You can use TOP in a view without an ORDER BY. What you cannot do it use an ORDER BY without TOP. In this case you would use ORDER BY with TOP 100 PERCENT.


USE pubs
GO
create view v_test_order
as
select au_id, au_lname
from dbo.authors
where au_lname like 'b%'

union all

select top 1 au_id, au_lname
from dbo.authors
where au_lname like 'd%'
GO
select * from v_test_order

Your view seems ok to me. The problem must be elsewhere.

Bambola.