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.

1.PNG

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.

2.PNG

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.

3.PNG

4.PNG

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.

/wp-content/uploads/2016/01/img_895097.png

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:

6.PNG

7.PNG

Espero que possa ser útil.

Dúvidas e sugestões são bem vindas.


Att,

Diego Lother


View Diego Lother's profile on LinkedIn

To report this post you need to login first.

10 Comments

You must be Logged on to comment or reply to a post.

  1. Augustus Pinto

    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

    (0) 
  2. Rodrigo da Costa Feula

    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

    (0) 
    1. DIEGO LOTHER Post author

      Olá Rodrigo,

      Obrigado pelo feedback.

      Estarei testando a sugestão proposta. Atualizarei o post assim que possivel. 🙂


      Atenciosamente,

      Diego Lother


      View Diego Lother's profile on LinkedIn

      (0) 

Leave a Reply