1. Apoio ao Suporte e Consultoria
  2. SQL uteis

SQL uteis

Introdução:

Este artigo tem como objetivo principal apresentar aos colaboradores da Vexta, de forma resumida, simples e com exemplos de aplicação em nosso dia a dia, alguns dos comandos do SQL que são essenciais em nosso cotidiano.

O artigo se divide em duas partes: explicação dos comandos básicos mais utilizados e aplicações práticas em nosso banco de dados, com situações e clientes reais.

 

Explicação dos comandos básicos mais utilizados:

SELECT: tem o propósito de re informações no banco de dados:

WHERE: filtra o que você precisa pesquisar através do SELECT de acordo com os parâmetros que você passou:

ORDER BY: ordena os resultados da consulta de acordo com os parâmetros especificados:

USE: é possível selecionar o banco de dados que queremos utilizar/manipular:

UPDATE: é responsável por atualizar dados já criados em nossa tabela;

  • INSERT: Com esse comando é possível inserir dados;
  • GROUP BY: é utilizado para agrupar linhas baseado em semelhanças entre elas;
  • ALTER: comando utilizado para alterar uma tabela ou um banco de dados já existente;
  • INNER JOIN: trazer dados de duas ou mais tabelas relacionadas:

 

Aplicação dos comandos na prática: 

Trazer todos os códigos de barras vinculados aos itens de um pedido de venda

Comando: EXEC PEDIDO_CODIGO_BARRAS "ID DA EMPRESA", "ID DO PEDIDO"
Exemplo:

 

Conferir os consumos da ordem em questão:

SELECT
OM.EMPRESA_ID,
OM.FILIAL_ID,
OM.DATA_CRIACAO AS DATACRIACAOOF
,FMC.DATA_CRIACAO AS DATACRIACAOCONSUMOSDAFICHA,
* FROM FABRICACAO F
LEFT JOIN FABRICACAO_CONSUMO FC ON FC.FABRICACAO_ID=F.ID
INNER JOIN ORDEM_MESTRE OM ON OM.ID=F.ORDEM_MESTRE_ID
INNER JOIN FICHA_MESTRE FM ON OM.FICHA_MESTRE_ID=FM.ID
INNER JOIN FICHA_MESTRE_CONSUMO FMC ON FMC.FICHA_MESTRE_ID=FM.ID
WHERE F.ORDEM_MESTRE_ID=16767

 

Enviar notas fiscais pelo banco de dados em conferência:

BEGIN TRAN
DECLARE @EMPRESA INT = ID DA EMPRESA ,@NF INT = ID DA NOTA ,@ERRO VARCHAR(8000)
EXEC NOTA_FISCAL_ELETRONICA_2G_ENVIAR @EMPRESA, @NF
EXEC NF_ELETRONICA_ITEM_2G_EX @EMPRESA, @NF, @ERRO OUTPUT
SELECT @ERRO
ROLLBACK

 

Conferir valores de Impostos no pedido de venda, IPI e ICMS:

SELECT
SUM(VALOR_BASE_CALCULO_IPI)OVER() AS VALOR_BASE_CALCULO_IPI
,PERCENTUAL_IPI
,SUM(VALOR_IPI)OVER() AS VALOR_IPI
,SUM(VALOR_BASE_CALCULO_ICMS)OVER() AS VALOR_BASE_CALCULO_ICMS
,PERCENTUAL_ICMS
,sum(VALOR_BASE_CALCULO_ICMS*(PERCENTUAL_ICMS/100))OVER() AS VALOR_ICMS
FROM TB_PEDIDO_VENDA_ITEM WHERE PEDIDO_VENDA_ID=858977

 

 

Conferir valores de Impostos na nota fiscal, IPI. ICMS, PIS e COFINS:

SELECT REGRA_IMPOSTO_ID_ICMS,
(QUANTIDADE*VALOR_UNITARIO_SEM_IPI) AS TOTALINDIVIDUAL
,VALOR_FRETE
,VALOR_SEGURO
,VALOR_DESPESA
,SUM(QUANTIDADE*VALOR_UNITARIO_SEM_IPI)OVER() AS TOTAL_PROD
,SUM(VALOR_BASE_CALCULO_IPI)OVER() AS VALOR_BASE_CALCULO_IPI
,PERCENTUAL_IPI
,SUM(VALOR_IPI)OVER() AS VALOR_IPI
,SUM(VALOR_BASE_CALCULO_ICMS)OVER() AS VALOR_BASE_CALCULO_ICMS
,PERCENTUAL_ICMS
,SUM(VALOR_BASE_CALCULO_ICMS*(PERCENTUAL_ICMS/100))OVER() AS VALOR_ICMS
,SUM(VALOR_BASE_CALCULO_PIS)OVER() AS VALOR_BASE_CALCULO_PIS
,PERCENTUAL_PIS
,SUM(VALOR_PIS)OVER() AS VALOR_PIS
,SUM(VALOR_BASE_CALCULO_COFINS)OVER() AS VALOR_BASE_CALCULO_COFINS
,PERCENTUAL_COFINS
,SUM(VALOR_COFINS)OVER() AS VALOR_COFINS
,*FROM TB_NOTA_FISCAL_ITEM WHERE NOTA_FISCAL_ID= 859012

 

 

Conferir a Base de Cálculo do ICMS-ST de uma nota fiscal:

SELECT
NFI.REGRA_IMPOSTO_ID_ICMS,
SUM((NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI)OVER() AS BCDICMS,
NFI.PERCENTUAL_ICMS,
SUM((NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI)OVER() *(NFI.PERCENTUAL_ICMS/100) AS VALORICMS,
NM.MVA,

SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()
*(NM.MVA/100)
AS RESULTADO,

(SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER())
AS BCICMSST,
NFI.PERCENTUAL_ICMS_INTERNO,

((SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()))
*(NFI.PERCENTUAL_ICMS_INTERNO/100)
AS VALORINICIALICMSST,

(((SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)
+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI
)OVER()))
*(NFI.PERCENTUAL_ICMS_INTERNO/100))
- (SUM((NFI.QUANTIDADE*NFI.VALOR_UNITARIO_SEM_IPI)+NFI.VALOR_DESCONTO_ACRESCIMO+NFI.VALOR_FRETE+NFI.VALOR_SEGURO+NFI.VALOR_DESPESA+NFI.VALOR_IPI)OVER() *(NFI.PERCENTUAL_ICMS/100))
AS VALOSICMSST
FROM TB_NOTA_FISCAL_ITEM NFI
INNER JOIN NOTA_FISCAL NF ON NF.ID=NFI.NOTA_FISCAL_ID
INNER JOIN TB_PESSOA P ON P.ID=NF.PESSOA_ID
INNER JOIN TB_MATERIAL M ON M.ID=NFI.MATERIAL_ID
INNER JOIN NCM N ON N.ID=M.NCM_ID
LEFT JOIN NCM_MVA NM ON NM.NCM_ID=N.ID
INNER JOIN MUNICIPIO MU on MU.ID=P.MUNICIPIO_ID_ENDERECO AND MU.ESTADO_SIGLA=NM.ESTADO_SIGLA_DESTINO
WHERE NOTA_FISCAL_ID=859046

 

 

Conferir a Base de Cálculo do ICMS-ST de um PEDIDO DE VENDA: 

SELECT
PVI.REGRA_IMPOSTO_ID_ICMS,
SUM((PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI)OVER() AS BCDICMS,
PVI.PERCENTUAL_ICMS,
SUM((PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI)OVER() *(PVI.PERCENTUAL_ICMS/100) AS VALORICMS,
NM.MVA,

SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()
*(NM.MVA/100)
AS RESULTADO,

(SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER())
AS BCICMSST,
PVI.PERCENTUAL_ICMS_INTERNO,

((SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()))
*(PVI.PERCENTUAL_ICMS_INTERNO/100)
AS VALORINICIALICMSST,

(((SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()
*(NM.MVA/100))
+(SUM(
(PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)
+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI
)OVER()))
*(PVI.PERCENTUAL_ICMS_INTERNO/100))
- (SUM((PVI.QUANTIDADE*PVI.VALOR_UNITARIO_SEM_IPI)+PVI.VALOR_DESCONTO_ACRESCIMO+PVI.VALOR_FRETE+PVI.VALOR_SEGURO+PVI.VALOR_DESPESA+PVI.VALOR_IPI)OVER() *(PVI.PERCENTUAL_ICMS/100))
AS VALOSICMSST
FROM TB_PEDIDO_VENDA_ITEM PVI
INNER JOIN PEDIDO_VENDA PV ON PV.ID=PVI.PEDIDO_VENDA_ID
INNER JOIN TB_PESSOA P ON P.ID=PV.PESSOA_ID
INNER JOIN TB_MATERIAL M ON M.ID=PVI.MATERIAL_ID
INNER JOIN NCM N ON N.ID=M.NCM_ID
LEFT JOIN NCM_MVA NM ON NM.NCM_ID=N.ID
INNER JOIN MUNICIPIO MU on MU.ID=P.MUNICIPIO_ID_ENDERECO AND MU.ESTADO_SIGLA=NM.ESTADO_SIGLA_DESTINO
WHERE PEDIDO_VENDA_ID=859045

 

 

Conferir impostos do pedido conferindo como os impostos da nota deste pedido:

SELECT
M.NOME
,PVI.VALOR_FRETE AS FRETE
,PVI.VALOR_SEGURO AS SEGURO
,PVI.VALOR_DESPESA AS DEPESAS
,PVI.VALOR_DESCONTO_ACRESCIMO AS DESCONTO
,PVI.REGRA_IMPOSTO_ID_IPI AS REGRA_IMPOSTO_IPI
,PVI.VALOR_BASE_CALCULO_IPI AS BC_IPI
,PVI.PERCENTUAL_IPI
,PVI.VALOR_IPI
,PVI.REGRA_IMPOSTO_ID_ICMS AS REGRA_IMPOSTO_ICMS
,VALOR_BASE_CALCULO_ICMS AS BC_ICMS
,PVI.PERCENTUAL_ICMS
,VALOR_BASE_CALCULO_ICMS*(PERCENTUAL_ICMS/100) as VALOR_ICMS
FROM TB_PEDIDO_VENDA_ITEM PVI
INNER JOIN PEDIDO_VENDA PV ON PV.ID=PVI.PEDIDO_VENDA_ID
INNER JOIN TB_PESSOA P ON P.ID=PV.PESSOA_ID
INNER JOIN TB_MATERIAL M ON M.ID=PVI.MATERIAL_ID
left JOIN NCM N ON N.ID=M.NCM_ID
LEFT JOIN NCM_MVA NM ON NM.NCM_ID=N.ID
left JOIN MUNICIPIO MU on MU.ID=P.MUNICIPIO_ID_ENDERECO AND MU.ESTADO_SIGLA=NM.ESTADO_SIGLA_DESTINO
WHERE PEDIDO_VENDA_ID=858977

SELECT
M.NOME
,NFI.VALOR_FRETE AS FRETE
,NFI.VALOR_SEGURO AS SEGURO
,NFI.VALOR_DESPESA AS DEPESAS
,NFI.VALOR_DESCONTO_ACRESCIMO AS DESCONTO
,NFI.REGRA_IMPOSTO_ID_IPI AS REGRA_IMPOSTO_IPI
,NFI.VALOR_BASE_CALCULO_IPI AS BC_IPI
,NFI.PERCENTUAL_IPI
,NFI.VALOR_IPI
,NFI.REGRA_IMPOSTO_ID_ICMS AS REGRA_IMPOSTO_ICMS
,NFI.VALOR_BASE_CALCULO_ICMS AS BC_ICMS
,NFI.PERCENTUAL_ICMS
,NFI.VALOR_ICMS
,NFI.REGRA_IMPOSTO_ID_PIS AS REGRA_IMPOSTO_PIS
,NFI.VALOR_BASE_CALCULO_PIS AS BC_PIS
,NFI.PERCENTUAL_PIS
,NFI.REGRA_IMPOSTO_ID_COFINS AS REGRA_IMPOSTO_COFINS
,NFI.VALOR_PIS,NFI.VALOR_BASE_CALCULO_COFINS AS BC_COFINS
,NFI.PERCENTUAL_COFINS
,NFI.VALOR_COFINS
FROM TB_NOTA_FISCAL_ITEM NFI
INNER JOIN NOTA_FISCAL NF ON NF.ID=NFI.NOTA_FISCAL_ID
INNER JOIN TB_PESSOA P ON P.ID=NF.PESSOA_ID
INNER JOIN TB_MATERIAL M ON M.ID=NFI.MATERIAL_ID
left JOIN NCM N ON N.ID=M.NCM_ID
LEFT JOIN NCM_MVA NM ON NM.NCM_ID=N.ID
left JOIN MUNICIPIO MU on MU.ID=P.MUNICIPIO_ID_ENDERECO AND MU.ESTADO_SIGLA=NM.ESTADO_SIGLA_DESTINO
WHERE PEDIDO_VENDA_ID=858977

 

 

COMO SABER OS USUÁRIOS QUE ESTÃO CADASTRADOS EM DETERMINADA BASE

select * from MICROCEL_LOG_SISTEMA

 

BASE DA COREMAX, CORRIGIR ID NA NSU

/*Violação da restrição PRIMARY KEY 'PK_NSU'. Não é possível inserir a chave duplicada no objeto 'dbo.NSU'. O valor de chave duplicada é (506, 2002114120)
ATENÇÃO: o sistema encontrou um número sequencial único cadastrado com o código "2002114120" e alterou o código deste novo cadastro para "2002115884".*/
exec gerar_id_excluir_duplicados
update tabela_gerar_id set valor =(select max(id) from nsu) where nome like 'NUMERO_SEQUENCIAL_UNICO_506'
select * from tabela_gerar_id where nome like 'NUMERO_SEQUENCIAL_UNICO_506'

 

 

ATENÇÃO: falha em update porque as seguintes opções set têm configurações incorretas: 'ansi_warnings'. verifique se as opções set estão corretas para uso com exibições indexadas e/ou índices em colunas computadas e/ou índices filtrados e/ou notificações de consulta e/ou métodos de tipo de dados xml e/ou operações de índice espacial.

Pela banco de dados, executar um alterar nf_ao_modificar, copiar a procedure, colar em outra aba, e mudar a linha

SET ANSI_WARNINGS OFF

para

SET ANSI_WARNINGS ON

Far um F5 para salvar a alteração e tentar enviar a nota fiscal novamente

 

================================

=================================

A NF-e foi rejeitada pelo SEFAZ de origem com a mensagem 434: Rejeição: NF-e sem indicativo do intermediador

Considerando que os problemas relatados foram solucionados, clique em Avançar para reenviar o documento.

Ir nas diretivas da tela de notas fiscais >> Gerar dados do intermediador >> sim

 

================================

=================================

 

Ao acusar a mensagem:

Este cliente possui configuração no parâmetro que está sendo excluído (ROMANEIO.PRECISAO_DECIMAL), '+
'verifique se os valores deste parâmetro são os mesmos de (PEDIDOVENDA.PEDIDOVENDAITEM.PRECISAO_DECIMAL)

Executar os seguintes comandos, conforme abaixo:

SELECT * FROM dbo.PARAMETROS_USUARIO (-1 ,-1 ,'ROMANEIO.PRECISAO_DECIMAL', '')

SELECT * FROM dbo.PARAMETROS_USUARIO (-1 ,-1 ,'PEDIDOVENDA.PEDIDOVENDAITEM.PRECISAO_DECIMAL', '')

Se aparecer resultado o primeiro select, ir na diretiva da tela de pedido de venda >> Itens do Pedido >> Quantidade de casas decimais que deve ser considerada e preencher o mesmo valor do primeiro select

 

================================

=================================

 

update material_codigo_barras set off_line=1-CODIGO_UNICO where material_id_mestre=65682

 

================================

=================================

Procurar notas para pessoas do tipo fisica e ordenar por ordem de saida

select * from nota_fiscal nf
inner join tb_pessoa p on p.id=nf.PESSOA_ID
where p.NATUREZA='F'
order by data_saida desc

 

Como saber quais das tabelas do banco de dados possuem determinado campo. Neste exemplo queremos achar a tabela que possui o campo "b2c"

SELECT
OBJECT_NAME(id) AS Tabela,
Name AS Coluna
FROM
sys.syscolumns
WHERE
name = 'b2c'

 

 

--ENCONTRAR DADOS OS PEDIDOS/COLEÇOES/MATERIAIS/DE DETERMINADOS REPRESENTANTES DE APP


select cm.data_venda_inicial,cm.data_venda_final,pv.data_digitacao as data,pv.filial_id,m.COLECAO_MATERIAL_ID,mls.COLECAO,mls.PESSOA_ID_REPRESENTANTE,* from TB_PEDIDO_VENDA pv
inner join MICROCEL_LOG_SISTEMA mls on mls.REPRESENTANTE_ID_PEDIDO=pv.PESSOA_ID_REPRESENTANTE
inner join TB_PEDIDO_VENDA_ITEM pvi on pvi.PEDIDO_VENDA_ID=pv.ID and pvi.EMPRESA_ID=pv.EMPRESA_ID
inner join TB_MATERIAL m on m.ID=pvi.MATERIAL_ID
inner join COLECAO_MATERIAL cm on cm.ID=m.COLECAO_MATERIAL_ID
where mls.COMENTARIO like 'felipe%' and GRUPO_USUARIO_ID=968 and cm.ID in (158,155)
order by DATA_DIGITACAO desc

 

 

Para verificar qual foi o último número gerado para NF-e´s em Homologação, segue consulta da tabela:

SELECT MAX(VALOR) FROM TABELA_GERAR_ID
WHERE NOME = 'NUMERO_NFE_HOMOLOGACAO'

Isto pode ser necessário caso acuse o erro de duplicidade ao enviar a nota em homologação, pois a rotina incrementa agora o numero da nota fiscal em homologação, e salvando essa informação na tabela gerar id. Caso este erro aconteça é necessário alterar o valor do NUMERO_NFE_HOMOLOGACAO até a duplicidade não existir mais.

 

--->>>Update para alterar o valor, caso necessário.

update tabela_gerar_id set valor = 'ultimo numero da nota em homologação' where nome = 'NUMERO_NFE_HOMOLOGACAO'

 

Select para saber em qual ambiente está a nota, o protocolo, a chave, e outros.

 

select NFE_RESULTADO_ID,NFE_AMBIENTE,NUMERO_SERIE,NFE2G_PROTOCOLO,NFE2G_CHAVE,NFE2G_AUTORIZADA,* from nota_fiscal where id = 'id da nota'

 

Para saber mais detalhes dos itens da nota, como por exemplo valor utilizado para cálculo:

 

BEGIN TRAN
DECLARE @EMPRESA INT = 3 ,@NF INT = 3023609 ,@ERRO VARCHAR(8000)
EXEC NOTA_FISCAL_ELETRONICA_2G_ENVIAR @EMPRESA, @NF
EXEC NF_ELETRONICA_ITEM_2G_EX @EMPRESA, @NF, @ERRO OUTPUT
SELECT @ERRO
ROLLBACK

Alguma dúvida?
Abrir chamado