Criando filtros customizados para consultas no gerente de consultas
Olá pessoal, essa semana passei por uma situação em que era necessário criar uma consulta com critérios de seleção customizados, e ao chegar no resultado final, acreditei que seria interessante compartilhar este artificio.
Bom vamos ao nosso cenário fictício:
Supomos que temos a necessidade de criar filtros para a seguinte consulta(chamarei de Consulta Situação Nota Fiscal):
SELECT
Chave,
[Número do Documento],
[Código do PN],
Status,
[Data de Lançamento],
[Data de vencimento],
[Data do Documento]
FROM
(SELECT
DocEntry AS 'Chave',
DocNum AS 'Número do Documento',
CardCode AS 'Código do PN',
CASE
WHEN CANCELED = 'Y' THEN
'Cancelada'
WHEN DocStatus = 'C' THEN
'Fechada'
WHEN DocStatus = 'O' THEN
'Aberta'
END AS Status,
DocDate AS 'Data de Lançamento',
DocDueDate AS 'Data de vencimento',
TaxDate AS 'Data do Documento'
FROM OINV) AS
T0
Para esta consulta criaremos 4 filtros que são:
1. Tipo de data que deve ser filtrado: Data de pagamento, Data de vencimento ou data de emissão.
2. Data Inicial
3. Data Final
4. Status do documento: Aberto, Fechado, Cancelado e Todos.
No entanto, de maneira tradicional, não é possível colocar nos critérios de seleção, o rotulo que você deseja em cada filtro. Também não é possível ter um combo com opções customizadas. Essa situação pode ser resolvida com a criação de uma UDT(Tabela de usuário) do tipo “sem objeto”.
Vamos a resolução:
Criarei uma tabela chamada Filtros.
Com a tabela criada, vamos a criação dos campos:
Os filtros Data Inicial e Data Final terão apenas uma nova descrição. Então criarei dois campos um com o titulo DtIni e com a descrição “Data Inicial”(estará na imagem abaixo) e outro com o titulo DtFim e com a descrição “Data Final”, ambos do tipo Data/Hora, com estrutura do tipo Data.
Agora vamos aos filtros onde é necessário um combo. Criaremos da mesma forma como apresentada acima, porém esses dois filtros serão do tipo alfanúmerico. O artificio para esses filtros é a adição de valores válidos. Dessa forma poderemos usar eles no combo da tela de critérios de seleção.
Na criação dos valores válidos, usarei no campo valor e no campo descrição a mesma informação.
Agora com nossas opções de filtros criadas, iremos chama-lá a partir do gerador de consulta da seguinte forma:
SELECT * FROM [@FILTROS] T0 WHERE T0.[U_TipoData] = '[%0]' AND
T0.[U_DtIni] >= '[%1]' AND T0.[U_DtFim] <= '[%2]' AND T0.[U_Status] = '[%3]'
Na instrução acima, estou apenas chamando os filtros , não estou aplicando eles na minha consulta ainda. O comando acima pode ser entendido como:
%0 = Apareça em tela um filtro relacionado ao campo TipoData da tabela @Filtros com o texto “Igual a”.
%1 = Apareça em tela um filtro relacionado ao campo DtIni da tabela @Filtros com o texto “Maior ou igual”.
%2 = Apareça em tela um filtro relacionado ao campo DtFim da tabela @Filtros com o texto “Menor ou igual”.
%3 = Apareça em tela um filtro relacionado ao campo Status da tabela @Filtros com o texto “Igual a”.
A linha acima será executada como consulta, mas não retornará nada, pois a tabela está vazia. Mesmo que retornasse algo, para esse nosso cenário, o retorno não importa.
Agora vamos ler esses filtros para variáveis, para que possamos usar nas nossas consultas.
DECLARE @TipoData AS NVARCHAR(20)
DECLARE @DtIni AS DATETIME
DECLARE @DtFim AS DATETIME
DECLARE @Status AS NVARCHAR(10)
/* SELECT FROM [@FILTROS] T0 WHERE */ SET @TipoData /* T0.[U_TipoData] = */ = '[%0]' /* AND */
SET @DtIni /* T0.[U_DtIni] >= */ = '[%1]' /* AND */ SET @DtFim /* T0.[U_DtFim] <= */ = '[%2]' /*
AND */ SET @Status /* T0.[U_Status] = */ = '[%3]'
Com esta alteração estamos fazendo a mesma coisa descrita acima, porém agora, estamos também pegando o valor obtido no filtro e colocando esse valor nas nossas variáveis. Nesse momento basta apenas alterar nossa consulta para utilizar as variáveis obtida. Dessa forma nosso resultado final ficará:
DECLARE @TipoData AS NVARCHAR(20)
DECLARE @DtIni AS DATETIME
DECLARE @DtFim AS DATETIME
DECLARE @Status AS NVARCHAR(10)
/* SELECT FROM [@FILTROS] T0 WHERE */ SET @TipoData /* T0.[U_TipoData] = */ = '[%0]' /* AND */
SET @DtIni /* T0.[U_DtIni] >= */ = '[%1]' /* AND */ SET @DtFim /* T0.[U_DtFim] <= */ = '[%2]' /*
AND */ SET @Status /* T0.[U_Status] = */ = '[%3]'
SELECT
Chave,
[Número do Documento],
[Código do PN],
Status,
[Data de Lançamento],
[Data de vencimento],
[Data do Documento]
FROM
(SELECT
DocEntry AS 'Chave',
DocNum AS 'Número do Documento',
CardCode AS 'Código do PN',
CASE
WHEN CANCELED = 'Y' THEN
'Cancelado'
WHEN DocStatus = 'C' THEN
'Fechado'
WHEN DocStatus = 'O' THEN
'Aberto'
END AS Status,
DocDate AS 'Data de Lançamento',
DocDueDate AS 'Data de vencimento',
TaxDate AS 'Data do Documento'
FROM OINV) AS
T0
WHERE
@DtIni <=
CASE
WHEN @TipoData = 'Data de Lançamento' THEN
T0.[Data de Lançamento]
WHEN @TipoData = 'Data de vencimento' THEN
T0.[Data de vencimento]
WHEN @TipoData = 'Data do Documento' THEN
T0.[Data do Documento]
END
AND
@DtFim >=
CASE
WHEN @TipoData = 'Data de Lançamento' THEN
T0.[Data de Lançamento]
WHEN @TipoData = 'Data de vencimento' THEN
T0.[Data de vencimento]
WHEN @TipoData = 'Data do Documento' THEN
T0.[Data do Documento]
END
AND
CASE
WHEN @Status = 'Todos' THEN
''
ELSE
T0.Status
END
=
CASE
WHEN @Status = 'Todos' THEN
''
ELSE
@Status
END
Ao executar nossa consulta, teremos a seguinte situação:
Espero que possa ser útil.
Dúvidas e sugestões são bem vindas.
Att,
Diego Lother
Muito Legal.....
Assim que puder vou Testar....
att.
Kelvin
Ribeiro,
Espero que ajude, obrigado pelo feedback.
Atenciosamente,
Diego Lother
Muito boa sua dica Diego Lother !!!
Com certeza irei utilizar, pois já me deparei com várias Consultas onde estas opções dariam uma melhor flexibilidade na entrada dos Parâmetros/Filtros.
Parabéns.
Augustus
Augustus,
Obrigado pelo feedback. Espero que ajude.
Atenciosamente,
Diego Lother
Diego.
Testei e funcionou perfeitamente. Parabéns !!!
Abraços.
Augustus
Olá Diego.
Parabéns pela sua publicação!!!
Excelente abordagem!!!
Já usei esta questão de definição de variáveis em um outro formato:
declare @teste nvarchar(max)
set @teste = /* t0.[CardCode] */ '[%0]'
SELECT * FROM OCRD T0 WHERE T0.[CardCode] = @teste
Neste cenário é possível usar um campo qualquer de uma das tabelas em select para fazer a definição da variável e a definição da variável não exige a execução de vários SELECTs.
Depois foi possível usar a variável em outras partes, inclusive em UNIONs e tudo mais.
Se possível dê uma olhada e veja se podemos melhorar o conjunto, assim conseguiremos otimizar consultas e tudo mais.
Abraço,
Rodrigo da Costa Feula
Olá Rodrigo,
Obrigado pelo feedback.
Estarei testando a sugestão proposta. Atualizarei o post assim que possivel. 🙂
Atenciosamente,
Diego Lother
Oi Diego, a visualização das tabelas onde são mostrados os códigos em SQL está comendo códigos aqui a direita.
Att,
Rodrigo da Costa Feula
Diego,
Ótimo post.
Obrigado por compartilhar.
Att,
Maicon Macedo
Olá Maicon,
Muito obrigado pelo feedback.
Atenciosamente,
Diego Lother
Ótimo post, ajudou eu a fazer consultas nas tabelas do Beas.
Atenciosamente,
Fabio.