none
Select Sem Union RRS feed

  • Pergunta

  • Boa....

    Gostaria de uma ajuda para melhorar uma query de uma rotina que tras um cálculo de valor de frete. Envolve uma tabela de regras de tranportadora e NF. A dificuldade é a seguinte:

    A situação: no cadastro de regras existem os municípios que junto com a capital tem o frete menor e uma regra com município em branco para os demais. Pelo estado da nota da pra saber o valor e condições do frete.

    A solução: Foi criada uma query repetitiva com union para realizar o cálculo. Sendo que a primeira query trás os valores se o município for achado e a segunda caso não ache.

    Problema: fica feio, confuso, difícil de fazer manutenção, etc.

    Alguém tem alguma idéia para lidar com isso???

    Obrigado!

    quinta-feira, 11 de agosto de 2011 19:03

Respostas

  • Eu diria para você pegar todos os casos em que precisa utilizar o ROUND (Apenas os que não estão dentro das cláusulas CASE), separe os em uma PROC para fazer os devidos UPDATEs e depois execute sua query sem os mesmos. Provavelmente a query terá desempenho melhor. A questão é que você terá que executar uma PROC separada para atualizar/arrendondar os dados.

    Assim você utiliza sua query apenas para fazer os CASE com ROUND dentro, as que você sempre arredonda, faz antes e apenas chame as colunas no SELECT.

    []'s


    DBA Vini
    • Sugerido como Resposta Eder Costa terça-feira, 23 de agosto de 2011 14:58
    • Marcado como Resposta Eder Costa sexta-feira, 26 de agosto de 2011 17:24
    segunda-feira, 15 de agosto de 2011 13:08

Todas as Respostas

  • Sidronio,

     

    Se possivel, poste a query para nos ajudar a ajuda-lo, para mim pelo menos ficou meio confuso sua explicação principalmente por não termos conhecimento de seu negocio.


    Fabrizzio A. Caputo
    Certificações:
    Oracle OCA 11g
    MCITP SQL Server 2008 Implementation and Maintenance
    MCITP SQL Server 2008 Developer
    Blog Pessoal: www.fabrizziocaputo.wordpress.com
    Blog Empresa: www.tripletech.com.br/blog
    Twitter: @FabrizzioCaputo
    Email: fabrizzio.antoniaci@gmail.com
    quinta-feira, 11 de agosto de 2011 19:14
    Moderador
  • DECLARE @VAR01 VARCHAR(8), @VAR02 VARCHAR(8), @VAR03 VARCHAR(09)

    SET @VAR01 = '20110715'
    SET @VAR02 = '20110722'
    SET @VAR03 = '000027403'

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT, FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, A1_COD_MUN,  ZC_MUN, ZC_FRTPERC, ZC_ICMS               
    , CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE ZC_FRTMIN END 'FRETE_VALOR'
    , ROUND(Floor(F2_PBRUTO) * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO'
    , CASE WHEN F2_PBRUTO <  100 THEN
      CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2) END --'FRETE_VALOR'
    ELSE
      ROUND(F2_PBRUTO * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE'
    , ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS'
    , ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SA1010 A1, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND A1.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND F2_CLIENTE = A1_COD
    AND F2_LOJA = A1_LOJA
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    --AND F2_EMISSAO BETWEEN @VAR01 AND @VAR02
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    and F2_SERIE != '999'
    AND ZC_MUN = A1_COD_MUN

    UNION ALL

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT,  FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, A1_COD_MUN,  ZC_MUN, ZC_FRTPERC, ZC_ICMS
    , CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE ZC_FRTMIN END 'FRETE_VALOR'
    , ROUND(F2_PBRUTO * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO'
    , CASE WHEN Floor(F2_PBRUTO) <  100 THEN
      CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2) END --'FRETE_VALOR'
    ELSE
      ROUND((F2_PBRUTO) * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE'
    , ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS'
    , ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SA1010 A1, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND A1.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND F2_CLIENTE = A1_COD
    AND F2_LOJA = A1_LOJA
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    and F2_SERIE != '999'
    --AND F2_EMISSAO BETWEEN @VAR01 AND @VAR02
    AND ZC_MUN = ''
    AND A1_COD_MUN NOT IN (SELECT ZC_MUN FROM SZC010 A WHERE A.D_E_L_E_T_ = '' AND F2_TRANSP = A.ZC_TRANSP AND F2_EST = A.ZC_EST AND A.ZC_MUN = A1_COD_MUN)

    UNION ALL

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT,  FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, A2_COD_MUN,  ZC_MUN, ZC_FRTPERC, ZC_ICMS
    , CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE ZC_FRTMIN END 'FRETE_VALOR'
    , ROUND(F2_PBRUTO * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO'
    , CASE WHEN F2_PBRUTO <  100 THEN
      CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2) END --'FRETE_VALOR'
    ELSE
      ROUND((F2_PBRUTO) * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE'
    , ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS'
    , ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SA2010 A2, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND A2.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND F2_CLIENTE = A2_COD
    AND F2_LOJA = A2_LOJA
    AND F2_TIPO != 'N'
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    --AND F2_EMISSAO BETWEEN @VAR01 AND @VAR02
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    and F2_SERIE != '999'
    AND ZC_MUN = A2_COD_MUN

    UNION ALL

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT,  FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, A2_COD_MUN,  ZC_MUN, ZC_FRTPERC,  ZC_ICMS
    , CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE ZC_FRTMIN END 'FRETE_VALOR'
    , ROUND(F2_PBRUTO * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO'
    , CASE WHEN F2_PBRUTO <  100 THEN
      CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN THEN ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2) ELSE round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2) END --'FRETE_VALOR'
    ELSE
      ROUND(F2_PBRUTO * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE'
    , ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS'
    , ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SA2010 A2, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND A2.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND A2.D_E_L_E_T_ = ''
    AND F2_CLIENTE = A2_COD
    AND F2_LOJA = A2_LOJA
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    AND F2_TIPO != 'N'
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    and F2_SERIE != '999'
    --AND F2_EMISSAO BETWEEN @VAR01 AND @VAR02
    AND ZC_MUN = ''
    AND A2_COD_MUN NOT IN (SELECT ZC_MUN FROM SZC010 A WHERE A.D_E_L_E_T_ = '' AND F2_TRANSP = A.ZC_TRANSP AND F2_EST = A.ZC_EST AND A.ZC_MUN = A2_COD_MUN)

    quinta-feira, 11 de agosto de 2011 19:17
  • Os 2 primeiros selects trabalham as notas de clientes, enquanto as 2 últimas outras notas (AND F2_TIPO != 'N').

    O primeiro select acha o valor caso o municípo da nota exista no cadastro de regra, já o segundo, trata quando não existe (AND A1_COD_MUN NOT IN (SELECT ZC_MUN FROM SZC010 A WHERE A.D_E_L_E_T_ = '' AND F2_TRANSP = A.ZC_TRANSP AND F2_EST = A.ZC_EST AND A.ZC_MUN = A1_COD_MUN

    Obrigado

    quinta-feira, 11 de agosto de 2011 19:19
  • Já esperimentou em vez de utilizar os unions utilizar subselects criando colunas? Pelo que vi as tuas consultas os filtros são os mesmos, o que facilita a sua utilização.
    José Ricardo Garcia
    quinta-feira, 11 de agosto de 2011 20:54
  • A primeira situação é tratar os munícipios das notas fiscais que estão na tabela de Regras de transportadoras e os que não estão. Nesse segundo caso retornaria o registro genérico do estado.

    Para ilustrar

    Est      Cod Municipio            Valor Frete

    RJ                                       10,00

    RJ        01500                      15,00

     

    Se uma nota tivesse o estado = RJ e municipio = 01500 retornaria 15,00, se tivesse o estado RJ e outro municipio retornaria 10,00.

    Poderia fazer uma query para ver se existe municipio e estado na tabela, senão houvesse faria outra para retornar o registro genérico (sem cod de municipio).

    Fazendo com union eu executo apenas uma vez.

    Mas tenho quase certeza que essa solução não é nem elegante nem a melhor.

    Alguma idéia?

    sexta-feira, 12 de agosto de 2011 11:41
  • Resolvi os dois primeiros selects assim:

    DECLARE @VAR01 VARCHAR(8), @VAR02 VARCHAR(8), @VAR03 VARCHAR(09), @VAR04 VARCHAR(5)

    SET @VAR01 = '20110715'
    SET @VAR02 = '20110722'
    SET @VAR03 = '000029539'

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT, FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, A1_COD_MUN,  ZC_MUN, ZC_FRTPERC, ZC_ICMS,               
    CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN
    THEN
     ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2)
    ELSE
     ZC_FRTMIN
    END 'FRETE_VALOR',
    ROUND(Floor(F2_PBRUTO) * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO',
    CASE WHEN F2_PBRUTO <  100
    THEN
     CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN
     THEN
      ROUND(F2_VALBRUT * (ZC_FRTPERC / 100)/((100 - ZC_ICMS)/100),2)
     ELSE
      round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2)
     END --'FRETE_VALOR'
    ELSE
     ROUND(Floor(F2_PBRUTO) * (ZC_FRTTON / 1000) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE',
    ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS',
    ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SA1010 A1, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND A1.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND F2_CLIENTE = A1_COD
    AND F2_LOJA = A1_LOJA
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    AND ZC_MUN =
     CASE
     WHEN EXISTS (SELECT * FROM SZC010 WHERE ZC_MUN = A1_COD_MUN)
     THEN
      A1_COD_MUN
     ELSE 
      ''
     END

    Agora no segundo caso, eu pego se o tipo != 'N' uma outra tabela, a SA2 ao invés da SA1 do primeiro caso.

    sexta-feira, 12 de agosto de 2011 13:11
  • Solução única para todos os casos. Pouco elegante:

    DECLARE @VAR01 VARCHAR(8), @VAR02 VARCHAR(8), @VAR03 VARCHAR(09), @VAR04 VARCHAR(5)

    SET @VAR01 = '20110715'
    SET @VAR02 = '20110722'
    SET @VAR03 = '000002996'

    SELECT F2_EST, F2_DOC, F2_SERIE, F2_VALBRUT, FLOOR(F2_PBRUTO) 'F2_PBRUTO', F2_TRANSP, ZC_MUN, ZC_FRTPERC, ZC_ICMS,               
    CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN
    THEN
     ROUND((F2_VALBRUT * (ZC_FRTPERC / 100))/((100 - ZC_ICMS)/100),2)
    ELSE
     ZC_FRTMIN
    END 'FRETE_VALOR',
    ROUND( (Floor(F2_PBRUTO) * (ZC_FRTTON / 1000)) / ((100 - ZC_ICMS)/100),2)  'FRETEPESO',
    CASE WHEN F2_PBRUTO <  100
    THEN
     CASE WHEN F2_VALBRUT * (ZC_FRTPERC / 100) > ZC_FRTMIN
     THEN
      ROUND((F2_VALBRUT * (ZC_FRTPERC / 100))/((100 - ZC_ICMS)/100),2)
     ELSE
      round(ZC_FRTMIN/((100 - ZC_ICMS)/100),2)
     END --'FRETE_VALOR'
    ELSE
     ROUND((Floor(F2_PBRUTO) * (ZC_FRTTON / 1000)) / ((100 - ZC_ICMS)/100),2)  --'FRETEPESO'
    END 'FRETE',
    ROUND((F2_VALBRUT * (ZC_GRISPER / 100)) /((100 - ZC_ICMS)/100),2)  'GRIS',
    ROUND((CEILING(F2_PBRUTO / ZC_PEDFRA) * ZC_PEDFX1) /((100 - ZC_ICMS)/100) ,2) 'PEDAGIO'
    FROM SF2010 F2, SZC010 ZC
    WHERE F2.D_E_L_E_T_ = ''
    AND ZC.D_E_L_E_T_ = ''
    AND F2_CLIENTE+F2_LOJA =
    CASE WHEN F2_TIPO = 'N'
     THEN (SELECT A1_COD+A1_LOJA FROM SA1010 WHERE A1_COD = F2_CLIENTE AND D_E_L_E_T_ = '')
     ELSE (SELECT A2_COD+A2_LOJA FROM SA2010 WHERE A2_COD = F2_CLIENTE AND D_E_L_E_T_ = '')
    END
    AND F2_DOC = @VAR03
    AND F2_SERIE = '5'
    AND F2_TRANSP = ZC_TRANSP
    AND F2_EST = ZC_EST
    AND ZC_MUN =
     CASE
     WHEN EXISTS
      (SELECT * FROM SZC010 WHERE ZC_MUN = 
       CASE WHEN F2_TIPO = 'N'
        THEN (SELECT A1_COD_MUN FROM SA1010 WHERE A1_COD = F2_CLIENTE AND D_E_L_E_T_ = '')
        ELSE (SELECT A2_COD_MUN FROM SA2010 WHERE A2_COD = F2_CLIENTE AND D_E_L_E_T_ = '')
       END
       AND F2_TRANSP = ZC_TRANSP AND D_E_L_E_T_ = '')
     THEN
      CASE WHEN F2_TIPO = 'N' THEN (SELECT A1_COD_MUN FROM SA1010 WHERE A1_COD = F2_CLIENTE AND D_E_L_E_T_ = '')
             ELSE (SELECT A2_COD_MUN FROM SA2010 WHERE A2_COD = F2_CLIENTE AND D_E_L_E_T_ = '') END
     ELSE 
      ''
     END

    Alguém tem alguma idéia para diminuir essas subquerys?

    sexta-feira, 12 de agosto de 2011 14:16
  • Eu diria para você pegar todos os casos em que precisa utilizar o ROUND (Apenas os que não estão dentro das cláusulas CASE), separe os em uma PROC para fazer os devidos UPDATEs e depois execute sua query sem os mesmos. Provavelmente a query terá desempenho melhor. A questão é que você terá que executar uma PROC separada para atualizar/arrendondar os dados.

    Assim você utiliza sua query apenas para fazer os CASE com ROUND dentro, as que você sempre arredonda, faz antes e apenas chame as colunas no SELECT.

    []'s


    DBA Vini
    • Sugerido como Resposta Eder Costa terça-feira, 23 de agosto de 2011 14:58
    • Marcado como Resposta Eder Costa sexta-feira, 26 de agosto de 2011 17:24
    segunda-feira, 15 de agosto de 2011 13:08