Quando inserimos cadastros, documentos de marketing ou transações no SAP Business One, o banco de dados armazena os dados. Uma vez que essas informações estão gravadas no banco de dados, podemos buscá-las por meio do próprio SAP (Gerador de consultas) e através do console presente no SAP HANA Studio/SQL.
Gerador de consultas #
Para utilizar o gerador de consultas o usuário deve observar o menu superior e selecionar o caminho Ferramentas – Consultas – Gerador de Consultas, conforme imagem abaixo.
Após o usuário clicar em Gerador de consultas a janela será carregada, de acordo com imagem a seguir. Preenchemos o campo destinado a tabela de busca e após selecionar TAB os campos (colunas) presentes na tabela foram carregados.
Antes de prosseguirmos com a criação da consulta, existem alguns parâmetros importantes que se compreendidos poderam facilitar o dia a dia em análises e relatórios, são eles:
No grid esquerdo
- No canto superior direito, o campo destinado ao preenchimento das tabelas que irão compor a consulta.
- O Botão X, remove a tabela selecionada e essa exclusão se estende ao ajuste dos joins.
- O Botão >>, carrega todos os campos para o campo selecionado à direita, por padrão o campo Selecionar vem selecionado.
Na parte central
- Nome, é o nome do campo no banco de dados.
- Descrição, é a descrição presente na tela do SAP.
No grid à direita
- No campo Selecionar (SELECT), o usuário deve definir os campos que serão apresentados ao executar o gerador.
- No campo De (FROM), a medida em que o usuário for selecionando as tabelas que farão parte da consulta o gerador vai criar a relação entre as tabelas inclusive ele define os Alias (apelido) de cada tabela. Devemos dar atenção à relação criada, pois em alguns casos, mesmo que a relação seja estabelecida resultado da consulta pode não é o correto.
- No campo Onde (WHERE), quando houver a necessidade de definir filtros esse campo deve ser preenchido. Quanto a quantidade de condições não há limites desde que estejam coerentes.
- No campo Ordenar (ORDER BY), caso seja necessário ordenar o resultado da consulta por algum campo basta inserí-lo neste campo.
- No campo Agrupar por (GROUP BY), via de regra utilizou max, top, sum tem que agrupar. Neste campo devem estar todos os campos presentes na consulta, com exceção a soma, max ou top.
Observação: a ordem dos parâmetros anteriormente mencionados segue a estrutura do SAP HANA Studio/SQL, dessa maneira, ao criar uma consulta manualmente, atente-se a ordem dos parâmetros. Se por exemplo, você tentar inserir um group by antes do where a consulta deve retornar erro.
- Botão Condições, ao selecionar o botão são exibidas.
- Condições, são opções de filtro que podem ser utilizadas.
- Variáveis, são utilizadas como parâmetros para emissão do relatório. Por exemplo, digamos que você queira emitir o relatório de contas a receber de um cliente específico, dessa forma, na consulta criamos uma condição onde o código do parceiro é igual a variável [%0], no momento em que o usuário executar o relatório será exibido o parâmetro código parceiro para que ele preencha.
Definições do banco de dados #
Tabelas: mães e filhas #
Uma característica importante é a identificação da tabela principal (mãe) e suas respectivas filhas, por exemplo, o cadastro de parceiro de negócios sua tabela mãe é a OCRD e suas filhas são CRD1, CRD2 e por aí vai. Observem o padrão, as tabelas principais tem como prefixo o “O”, assim como as filhas perdem esse prefixo e recebem um número como sufixo. Utilizando outro exemplo, a tabela principal (cabeçalho) da nota fiscal de entrada é OPCH, essa janela tem outras tabelas como PCH1 (Linhas), PCH6 (Parcelas).
Relação entre tabelas #
No banco de dados existe uma coluna que é chamada de primary key, por meio dela é que as tabelas mãe e filhas se relacionam.
- OCRD (Cadastro de parceiros de negócios) – CardCode (Primary Key)
- OPCH (Nota fiscal de entrada) – DocEntry (Primary Key)
- OINV (Nota fiscal de saída)- DocEntry (Primary key)
Para encontrar mais informações relativas à relacionamento de tabelas, visito o site Biuan.
Utilizando o gerador de consultas, criei uma consulta.
Na imagem acima, selecionamos as tabelas OINV (Mãe) e INV1 (filha). No campo De, é possível identificar que o gerador de consultas relacionou (fez o join) entre as tabelas, para utilizou o DocEntry (Primary Key).
Alias (Apelido) #
Outra dúvida recorrente é quanto ao Alias (apelido) que deve ser utilizado para relacionar as tabelas. Observem que o próprio gerador possui um padrão, onde ao selecionarmos a primeira tabela o gerador já define o alias como T0, e a medida em que formos inserido novas tabela o gerador define novos apelidos.
O que define o alias a ser utilizado? O usuário. O único cuidado que devemos ter é, não definir nenhuma palavra que para o Banco de dados seja uma função, pois isso pode gerar erros ou ainda realizar um procedimento no banco.
O alias é necessário porque um relatório ou consulta utilizam de informações de diferentes tabelas e quando juntamos essas informações alguns campos, no banco de dados, possuem nomes similares. Por exemplo, imagine um cenário em que você deseja elaborar um relatório em que retorno dentre várias informações da nota fiscal de saída o nome fantasia do cliente. Ao criar essa consulta, você vai verificar que na OINV não existe o nome fantasia do cliente, dessa forma, será necessário criar um join para poder buscar informações do cadastro de parceiros de negócios.
Select T0.”CardCode”, T0.”DocTotal”, T1.”AliasName” from OINV T0
INNER JOIN OCRD T1 on T1.”CardCode” = T0.”CardCode”
Descrevendo o exemplo acima, criamos uma relação entre a OINV e OCRD onde o elo entre as duas tabelas é o “CardCode”. Observe o seguinte trecho T0.”CardCode”, T0.”DocTotal”, T1.”AliasName”, inserimos os apelidos conforme definimos. Se por ventura, decidissémos não inserir o alias do “CardCode” um erro seria retornado, esse erro se deve ao fato de possuirmos CardCode tanto na OINV quanto na OCRD. O que ocorre na prática é que o banco não entende de onde ele deve buscar o código do parceiro.
Além disso, uma vez realizada a junção entre as tabelas você pode buscar qualquer informação da tabela. Imagine o cenário que é necessário inserir o e-mail presente na aba geral do cadastro.
Select T0.”CardCode”, T0.”DocTotal”, T1.”AliasName”, T1.”E_Mail” from OINV T0
INNER JOIN OCRD T1 on T1.”CardCode” = T0.”CardCode”
Note que simplesmente inseri o campo com seu respectivo apelido como a informação está na tabela OCRD, utilizei o alias T1.”nome do campo”.
JOINS #
Para facilitar a compreensão dos joins, imagine o cenário em que foram emitidas 30 notas e durante a rotina financeira alguns desses documentos foram recebidos. Agora o usuário precisa emitir um relatório financeiro que retorne o número da nota e o valor dos recebimentos.
INNER JOIN #
O INNER JOIN é uma forma de unir informações entre diferentes tabelas, sua característica é retornar a interseção entre os dados.
Digamos que dos 30 documentos emitidos apenas 10 possuem valor baixado no financeiro, dessa forma, se utilizarmos o INNER JOIN o resultado da consulta será:
| Número da Nota | Valor Recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
LEFT JOIN #
O LEFT JOIN tem como característica retornar os dados à esquerda das tabelas. No exemplo utilizado anteriormente, o resultado utilizando o LEFT JOIN será.
| Número da nota | Valor recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
| 20+ | null (vazio) |
Observe que o resultado da consulta é igual a quantidade total de notas, e a coluna relativa a tabela do financeiro para notas que não possuem baixas retorna como null (vazio).
RIGHT JOIN #
O RIGHT JOIN tem como caraterística retornar dados à direita da junção. Para prosseguirmos utilizando o cenário, precisamos incrementar mais um detalhe. Imagine que na tabela de contas a receber temos 15 registros, dessa maneira, o resultado da consulta será.
| Número da nota | Valor recebido |
|---|---|
| 1 | 10,00 |
| 2 | 15,00 |
| 3 | 20,00 |
| 4 | 23,00 |
| 5 | 22,00 |
| 6 | 30,00 |
| 7 | 35,00 |
| 8 | 32,00 |
| 9 | 100,00 |
| 10 | 200,00 |
| null | 250,00 |
| null | 232,00 |
| null | 1000,00 |
| null | 12,00 |
| null | 13,00 |
No resultado, observe que agora possuímos 15 linhas que se refere a quantidade total de registros presentes na tabela de contas a receber. Além disso, note que as notas que possuem valor baixado no contas a receber retornaram número de nota, as outras 5 linhas retornaram null (vazio) isso pode ser explicado pelo fato de que os valores são oriúndos de outros documentos diferentes da Nota fiscal como por exemplo, adiantamentos.
Querys #
Criando o primeiro Select #
Para buscarmos todas as informações de uma determinada tabela por meio do gerador de consultas, tomando como exemplo a tabela OCRD – Parceiros de Negócios basta inserir a tabela no campo destinado e selecionar o Botão Executar.
Observe todos os dados da tabela (Principal) de parceiros de negócios são retornados.
Busca de colunas específicas #
Para retornar campos específicos o usuário deve procurar na relação de campos e clicar duas vezes com esquerdo do mouse que o campo seja copiado para o campo de Selecionar.
Em um base de homologação ou demonstração essa query roda muito bem, mas em determinados ambientes essa consulta pode não ter a mesma performace o que vai determinar isso é o volume de dados que a consulta deve retornar
Para compreender melhor a criação de consultas, gravei um vídeo onde demonstro como utilizar o gerador de consulta, além de gerar algumas querys.
