Skip to main content

SQL (Query) - Dicas e Funções

No agilityflow você pode buscar os dados utilizando SQL, através de queries. As queries devem ser compatíveis com SQL Server. Além disso existem alguns padrões que você deve seguir para obter um melhor resultado no agilityflow. Abaixo estão alguns detalhes importantes.

Tratamento de Registros Deletados

IMPORTANTE:
É de extrema importância o tratamento de registros deletados, usando as regras abaixo.  

Já está no nosso Roadmap o tratamento automático dos deletados, enquanto isso, o tratamento se torna obrigatório pelo desenvolvedor.

Para manter a integridade dos dados, o agilityflow nunca apaga um registro de uma tabela. Apenas o marca como deletado. Portanto, para qualquer query, é preciso excluir os deletados da lista.

Essa marcação é feita com campo deletado. Se o valor for 0, significa que ele não foi deletado e está visível no sistema. Caso esteja 1, significa que o registro já foi excluído e não é possível mais vê-lo na listagem principal do sistema.

Exemplo 1, sem o tratamento de deletados:

select usu_nome, usu_email from tbl_usuario

Resultado:

Exemplo 2, com o tratamento de deletados:

select usu_nome, usu_email from tbl_usuario
where deletado = 0

Resultado:

 

 

 

Campo do tipo Datetime (Data e Hora)

Sempre que um campo de texto com máscara do tipo data é criado, o sistema cria uma cópia com o mesmo nome seguido de "__datetime__" essa informação é gravada em uma coluna no banco de dados do tipo datetime, ao invés de varchar. Esses campos são criados para facilitar o uso tipado do dado nas queries

Exemplo de campo Datetime

Por exemplo, se o campo se chama "data_e_hora", haverá um outro chamado "data_e_hora__datetime__"  e o conteúdo estará no formato padrão do sql server, YYYY-MM-DD hh:mm:ss 

O campo "data_e_hora" estará sempre como varchar e o "data_e_hora__datetime__" estará como datetime 

 

 

Campo do tipo Numérico (decimal, float, double, int, number)

Sempre que um campo de texto com máscara do tipo número é criado, o sistema cria uma cópia com o mesmo nome seguido de "__number__" essa informação é gravada em uma coluna no banco de dados do tipo numeric(18,6), ao invés de varchar. Esses campos são criados para facilitar o uso tipado do dado nas queries

Exemplo de campo numérico

Por exemplo, se o campo se chama "numero_exemplo", haverá um outro chamado "numero_exemplo__number__"  e o conteúdo estará no formato padrão do sql server.

O campo "numero_exemplo" estará sempre como varchar e o "numero_exemplo__number__" estará como numeric(18,6)

 

 

 

Formatação de números 

Nome da Função

format(number,format,idioma)

Parâmetro: number

Pode ser um número inteiro ou um decimal

Parâmetro: format

Aqui você passa a formatação que deve ser retornada:

'n0' = retorna o número sem nenhuma casa decimal
'n1' = retorna o número com 1 casa decimal
'n2' = retorna o número com 2 casas decimais
'n3' = retorna o número com 3 casas decimais
'n4' = retorna o número com 4 casas decimais
'n5' = retorna o número com 5 casas decimais
'n6' = retorna o número com 6 casas decimais

Parâmetro: idioma - @sysCurrentLanguage

Aqui você precisa passar o idioma do usuário logado, pois alguns idiomas invertem o . (ponto) e a , (virgula) do número.
O agilityflow guarda o idioma do usuário dentro da variável @sysCurrentLanguage então apenas passe no parâmetro esse variável.

Exemplo de utilização

Abaixo o número será formato para 5 casas decimais, no padrão do idioma do usuário logado.

select FORMAT(5800000.888, 'n5', @sysCurrentLanguage)

Retorno para o usuário que está logado no agilityflow em português ou espanhol

Retorno para o usuário que está logado no agilityflow usando em inglês

 

 

 

Relatório - Filtro

Sempre que um relatório possuir um filtro, é necessário incluir esse filtro na query que gera os dados do relatório.

Não importa como é a query, ela deve incluir a cláusula where, como a chamada da função Filter. Essa função, possui 3 parâmetros:

Filter (variável_filtro, tabela, campo)

  • variável_filtro: é a variável que o agilityflow criou, após a configuração do filtro.
  • tabela: nome da tabela (definido nos Dados Técnicos) onde se encontra o dado a ser filtrado.
  • campo: é o nome do campo (conforme informado no campo Coluna Banco de Dados SQL) onde a informação que o filtro utiliza se encontra.

No exemplo abaixo, a query lista os campos usu_nome, usu_email e usu_sexo da tabela do sistema de usuário (tbl_usuario) e se inclui a função Filter, usando a variável @sexo aplicada no campo usu_sexo.

select usu_nome, usu_email, usu_sexo from tbl_usuario 
where Filter(@sexo, tbl_usuario, usu_sexo)

Exemplo de relatório sem filtro

Baseado na query acima, mas sem filtro configurado no relatório.

select usu_nome, usu_email, usu_sexo from tbl_usuario

Exemplo de relatório com filtro

O mesmo exemplo acima, com o filtro no campo sexo.

select usu_nome, usu_email, usu_sexo from tbl_usuario 
where Filter(@sexo, tbl_usuario, usu_sexo)

relatorio_filtro.gif

Caso seja criado um novo filtro, basta colocar "and" depois do primeiro Filter, e colocar o segundo Filter. Não importa a quantidade de filtros, desde que sejam adicionados todos os Filter e os and.

select usu_nome, usu_email, usu_sexo from tbl_usuario 
where Filter(@sexo, tbl_usuario, usu_sexo) and
      Filter(@nome, tbl_usuario, usu_nome) and
      Filter(@email, tbl_usuario, usu_email)

 

Lista Dinâmica - Regras

Os campos que são carregados com Lista dinâmica, podem ser customizados utilizando Query:

Algumas regras importantes:

1 - O resultado final da query precisa sempre ser os dados do formulário que você usou como base de dados

2 - Lembre-se de tratar os dados que já foram deletados, usando no where "...deletado = 0.."

3 - A query deve retornar duas colunas com os seguintes alias: "Name" e "Value". O "Name" deve ser a mesma coluna definida no campo de apresentação (Nome) e o "Value", deve ser a coluna "id" do formulário definido como base de dados (Cliente). Exemplo:

4 - Na query, o campo de apresentação "Name" pode ser um campo concatenado entre outras colunas.

Exemplo simples:


SELECT Id as Value, [CAMPO_DE_APRESENTACAO] as Name FROM [TABELA] where deletado = 0

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Back to top