# 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!

segunda-feira, 28 de maio de 2007 17:11

### Todas as Respostas

•

vc. tem que alterar esse sub select e colocalo em um join para usar o case em view, vc. pode colocar todo o codigo para gente olhar ? tente tambem criar a view pelo query analiser

create view nome_view

as

select ....

segunda-feira, 28 de maio de 2007 17:37
•

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,
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

segunda-feira, 28 de maio de 2007 17:50
• 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.