Problema com View no 2005

Question

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!

Answer 1

 

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

 

 

Answer 2

 

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

 

 

Answer 3

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