Inquiridor
Problema com View no 2005

Pergunta
-
Boa tarde!
Esse case abaixo, funciona perfeitamente em um select simples.
O problema é que ele nao funciona dentro de uma view. E preciso dele na view. Alguma explicacao?
" CASE
WHEN (select VLRTOTAL
from tbnfitem it with (nolock)
where it.numnf = tbnf.numnf
and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
and it.codevento = 108) in (0,null) THEN '0000000000'
WHEN (select VLRTOTAL
from tbnfitem it with (nolock)
where it.numnf = tbnf.numnf
and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
and it.codevento = 108) > 0 THEN '00000002.5'
END AS PERCENTUALDESPESA,
"Abracos!
Todas as Respostas
-
-
O codigo completo
create view Viewmovrm
as
select 'M0000104 01 01 ' as COLIGADAFILIALLOCAL,
CAST(tbfornec.codfornecexterno AS CHAR(25)) as codcfo, -- codcfo
RIGHT(' ',25) as constante1,
RIGHT(' '+ CAST(NUMNF AS CHAR(12)),12) as NUMEROMOV, --numeromov
'1 ' as serie, --serie,
'1.2.45 ' as codtmv, --codigo do tipo de movimento,
'P' as tipo,
'N' as status,
'000' as impresso,
DATAEMISSAO AS DATAEEMISSAO,
DATAEMISSAO AS DATASAIDA,
' 0000000000 00000' as datasextraREPRESENTANTE,
VLRTOTAL as VALORBRUTO,
(VLRTOTAL-(select isnull(isnull(VLRTOTAL,0),0)
from tbnfitem it with (nolock)
where it.numnf = tbnf.numnf
and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
and it.codevento = 108)) as VALORLIQUIDO,
VLRICMS as VALOROUTROS,
' 000000000000000000000000000000000000000000000000000000000000' AS OBSERVACAO,
-- CASE
-- WHEN (select VLRTOTAL
-- from tbnfitem it with (nolock)
-- where it.numnf = tbnf.numnf
-- and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
-- and it.codevento = 108) in (0,null) THEN '0000000000'
-- WHEN (select VLRTOTAL
-- from tbnfitem it with (nolock)
-- where it.numnf = tbnf.numnf
-- and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
-- and it.codevento = 108) > 0 THEN '00000002.5'
-- END AS PERCENTUALDESPESA,
-- CASE WHEN (select isnull(VLRTOTAL,0)
-- from tbnfitem it with (nolock)
-- where it.numnf = tbnf.CODGRUPONUMNF
-- and it.CODGRUPONUMNF = tbnf.numnf
-- and it.codevento = 108) in (0,null) THEN '0000000000'
-- WHEN (select isnull(VLRTOTAL,0)
-- from tbnfitem it with (nolock)
-- where it.numnf = tbnf.numnf
-- and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
-- and it.codevento = 108) <> 0 THEN (select sum(VLRTOTAL)
-- from tbnfitem it with (nolock)
-- where it.numnf = tbnf.numnf
-- and it.CODGRUPONUMNF = tbnf.CODGRUPONUMNF
-- and it.codevento = 108)
-- END AS valordespesa,
'0000000000000000000000000000000000000000 0000000000 00000000000000000000 000000000000 ' AS CONSTANTEPERCENTUALVALOR,
'1 ' as Campolivre3,
'R$ ' AS MoedaDatabaseMovimento,
DATAEMISSAO AS DATABASEMOVIMENTO,
'000000000000000 mestre 00000 0000100000767 0000000000 0000000000 0000000000 0000000000 0000000000 00001 00001 00001000000000000000 00000000000000000000 00000' AS CONSTANTE2,
case tbnf.CODGRUPOFISCAL
when 1 then '1.101'
when 2 then '2.101'
when 4 then '1.101'
END AS NATUREZA,
MES, ANO, CODGRUPOFISCAL, baseicms
from tbnf tbnf with (nolock) inner join tbfornec with (nolock) on tbnf.codfornec = tbfornec.codfornec
where CODGRUPOFISCAL = 1
-- and ano = 2007 and mes = 1 -
Oi Felipe,
Você poderia usar uma função para retornar a informação necessária, colocando o esse seu CASE inteiro dentro dela e passando como parâmetros, as informações que está usando no WHERE.
Sempre lembrando, se forma uma grande quantidade de dados, isso pode não ter uma performance muito interessante, mas isso irá ocorrer na sua VIEW da mesma forma.
Espero ter ajudado.
[]'s