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
- SAP Managed Tags:
11 Comments
