Série da semana: Primeiros passos no React

Veja mais

Consulas e subconsultas em varias tabelas

15/03/2016

1

Boa tarde, estou tentando utilizar uma query com subquerys onde preciso unir varias tabelas em uma unica view. Tenho tabelas separas para lançamento de depositos, aplicacao, resgate, cheques e nao estou conseguindo unir numa unica query todas essas tabelas. A query deve pegar o campo da conta bancaria e pesquisar em todas essas tabelas e unir os dados caso tenha registro cadastro nessa conta e ainda filtrar por data e ordenar tb por data. Nao sei se fui claro do que estou precisando, mas preciso de ajuda para montar a query. obrigado
Responder

Post mais votado

15/03/2016

Iramar,

É complicado tentar lhe ajudar com esse nivel de detalhes.

Coloque aqui a descrição da estrutura das tabelas ( com enfase nas chaves primárias e estrangeiras ) e/ou um diagrama de relacionamento delas.

Depois coloque a query ( ou queries ) que você tentou implementar.
Responder

Mais Posts

16/03/2016

Iramar Junior

Iramar,

É complicado tentar lhe ajudar com esse nivel de detalhes.

Coloque aqui a descrição da estrutura das tabelas ( com enfase nas chaves primárias e estrangeiras ) e/ou um diagrama de relacionamento delas.

Depois coloque a query ( ou queries ) que você tentou implementar.



_______________________________________________________________________________________________________________________________________________

Bom dia Marcos, segue o DER

[img]http://arquivo.devmedia.com.br/forum/imagem/196216-20160316-090941.png[/img]


Nota que todas as tabelas de movimentos bancarios dependem da tbl contabancaria, o filtro que estou tentando fazer tem que varrer todas as tabelas para fazer o filtro de acordo com o codigo da conta informado pelo usuario e tb entre o periodo de data que ele escolhe. Isso para fazer um relatorio de extrato bancario. Fui claro agora?
Responder

16/03/2016

Marcos P

Já tem alguma query ( mesmo que não funcione ) ?
Responder

16/03/2016

Iramar Junior

Já tem alguma query ( mesmo que não funcione ) ?


______________________________________________________________________________________________________________________________________________

Tenho esta query, so que nao me traz nenhum registro....

select
   cta.codcontabancaria,
   cta.numeroconta,
   lanc.codhiistorico,
   (select hst.nome from tbl_historico hst where hst.codhistorico = lanc.codhiistorico) as hst_nome_lanc,
   lanc.documento,
   lanc.vlr_lancamento,
   lanc.data_operacao,
   lanc.tipo_lanc as Tipo_lancamento,
   chq.codfornecedor,
   (select forn.nome from tbl_fornecedor forn where forn.codfornecedor = chq.codfornecedor) as forn_nome,
   chq.numerocheque,
   chq.valor,
   chq.emissao,
   (select hst.nome from tbl_historico hst where hst.codhistorico = chq.codhistorico) as hst_nome_chq,
   (select hst.nome from tbl_historico hst where hst.codhistorico = dep.codhistorico) as hst_nome_dep,
   dep.data_deposito,
   dep.valor,
   dep.tipo_lanc as tipo_deposito,
   (select hst.nome from tbl_historico hst where hst.codhistorico = deb.codhistorico) as hst_nome_deb,
   deb.data_debito,
   deb.valor,
   deb.tipo_lanc as tipo_debito,
   (select hst.nome from tbl_historico hst where hst.codhistorico = apl.codhistorico) as hst_nome_apl,
   apl.data_aplicacao,
   apl.valor,
   apl.tipo_lanc as tipo_aplicacao,
   (select hst.nome from tbl_historico hst where hst.codhistorico = res.codhistorico) as hst_nome_res,
   res.data_resgate,
   res.valor,
   res.tipo_lanc as tipo_resgate
from tbl_contabancaria cta, tbl_lancbancario lanc, tbl_cheque chq, tbl_deposito dep,
     tbl_debito deb, tbl_aplicacao apl, tbl_resgate res, tbl_historico hst, tbl_fornecedor forn
where
   cta.codcontabancaria = 2 and
   lanc.data_operacao >= '01-01-2016' and
   lanc.data_operacao <= '12-31-2016' and
   chq.emissao >= '01-01-2016' and
   chq.emissao <= '12-31-2016' and
   dep.data_deposito >= '01-01-2016' and
   dep.data_deposito <= '12-31-2016' and
   deb.data_debito >= '01-01-2016' and
   deb.data_debito <= '12-31-2016' and
   apl.data_aplicacao >= '01-01-2016' and
   apl.data_aplicacao <= '12-31-2016' and
   res.data_resgate >= '01-01-2016' and
   res.data_resgate <= '12-31-2016'
Responder

16/03/2016

Marcos P

Falta, nesse modelo, a descrição das chaves envolvidas.

Assumindo que TBL_CONTABANCARIA.CODCONTABANCARIA é a chave primária que liga a tabela principal às tabelas relacionadas, ficamos com :

select conta.*, apl.CodAplicacao, apl.Valor, chq.NumeroCheque, chq.Valor
from tbl_contabancaria conta left join tbl_aplicacao apl on (conta.codcontabancaria = apl.codcontabancaria and apl.data_aplicacao >= @paramData and apl.data_aplicacao <= @paramData )
                             left join tbl_cheque    chq on (conta.codcontabancaria = chq.codcontabancaria and chq.emissao >= @paramData and chq.emissao <= @paramData )
                             left join tbl_debito    deb on (conta.codcontabancaria = deb.codcontabancaria and deb.data_debito >= @paramData and deb.data_debito <= @paramData )
where conta.codcontabancaria = @paramConta


Inclua as demais tabelas e colunas, conforme sua necessidade.

Se for necessario tratar os NULL retornados, utilize ( case ) ou trate isso do lado da aplicação.
Responder

16/03/2016

Iramar Junior

Falta, nesse modelo, a descrição das chaves envolvidas.

Assumindo que TBL_CONTABANCARIA.CODCONTABANCARIA é a chave primária que liga a tabela principal às tabelas relacionadas, ficamos com :

select conta.*, apl.CodAplicacao, apl.Valor, chq.NumeroCheque, chq.Valor
from tbl_contabancaria conta left join tbl_aplicacao apl on (conta.codcontabancaria = apl.codcontabancaria and apl.data_aplicacao >= @paramData and apl.data_aplicacao <= @paramData )
                             left join tbl_cheque    chq on (conta.codcontabancaria = chq.codcontabancaria and chq.emissao >= @paramData and chq.emissao <= @paramData )
                             left join tbl_debito    deb on (conta.codcontabancaria = deb.codcontabancaria and deb.data_debito >= @paramData and deb.data_debito <= @paramData )
where conta.codcontabancaria = @paramConta


Inclua as demais tabelas e colunas, conforme sua necessidade.

Se for necessario tratar os NULL retornados, utilize ( case ) ou trate isso do lado da aplicação.


________________________________________________________________________________________________________________________________________

Quase deu certo, nao consigo fazer aparecer os historicos de acordo com que esta cadastrada em cada tabela e tb quando filtra acho que teria que agrupar pelas datas filtradas pq olha o que aconteceu. cheques cadastrados num periodo de '01/04/2014' a 30/04/014 e somente um lancamento bancario na data de hj, passei como parametro '01-04-2014' ate '16-03-2016'

[img]http://arquivo.devmedia.com.br/forum/imagem/196216-20160316-110009.png[/img]


select conta.numeroconta AS Conta_Bancaria,
lc.vlr_lancamento AS Valor_Lancamento,
chq.valor AS Valor_Cheque,
apl.valor AS Valor_Aplicacao,
dep.valor AS Valor_Deposito,
deb.valor AS Valor_Debito,
res.valor AS Valor_Resgate
from tbl_contabancaria conta left join tbl_aplicacao apl on (conta.codcontabancaria = apl.codcontabancaria and apl.data_aplicacao >= :paramDataI and apl.data_aplicacao <= :paramDataF )
                             left join tbl_cheque    chq on (conta.codcontabancaria = chq.codcontabancaria and chq.emissao >= :paramDataI and chq.emissao <= :paramDataF )
                             left join tbl_debito    deb on (conta.codcontabancaria = deb.codcontabancaria and deb.data_debito >= :paramDataI and deb.data_debito <= :paramDataF )
                             left join tbl_deposito  dep on (conta.codcontabancaria = dep.codcontabancaria and dep.data_deposito >= :paramDataI and dep.data_deposito <= :paramDataF )
                             left join tbl_resgate   res on (conta.codcontabancaria = res.codcontabancaria and res.data_resgate >= :paramDataI and res.data_resgate <= :paramDataF )
                             left join tbl_lancbancario lc on (conta.codcontabancaria = lc.codcontabancaria and lc.data_operacao >= :paramDataI and lc.data_operacao <= :paramDataF )
where conta.codcontabancaria = :paramConta
Responder

16/03/2016

Marcos P

Nenhuma dessas telas que você passou, tem o campo data para entender o "problema" que você descreveu, relacionado a data... ou seja, não entendi o problema reportado !

A parte de "agrupar", também não entendi...

Sugestão : deixe apenas uma tabela para fins de teste e resolva para uma tabela. Depois disso feito, as demais vão seguir o mesmo padrão.

Retorne aqui com uma descrição mais clara em apenas uma tabela.
Responder

16/03/2016

Iramar Junior

Nenhuma dessas telas que você passou, tem o campo data para entender o "problema" que você descreveu, relacionado a data... ou seja, não entendi o problema reportado !

A parte de "agrupar", também não entendi...

Sugestão : deixe apenas uma tabela para fins de teste e resolva para uma tabela. Depois disso feito, as demais vão seguir o mesmo padrão.

Retorne aqui com uma descrição mais clara em apenas uma tabela.


________________________________________________________________________________________________________________________________________

Se vc analisar o DER vera que todas as tabelas tem data de movimentacao para cada uma delas, entao quando faço o select tem que vir por data cronologica dos lancamentos de cada tabela, igual ao extrato bancario que tiramos nos caixas dos bancos. Entendeu agora?
Responder

16/03/2016

Iramar Junior

Nenhuma dessas telas que você passou, tem o campo data para entender o "problema" que você descreveu, relacionado a data... ou seja, não entendi o problema reportado !

A parte de "agrupar", também não entendi...

Sugestão : deixe apenas uma tabela para fins de teste e resolva para uma tabela. Depois disso feito, as demais vão seguir o mesmo padrão.

Retorne aqui com uma descrição mais clara em apenas uma tabela.


Passou perto com essa query, porem, nao consigo order por data e em todas as querys tem que haver o mesmo numero de campos, caso algum select tiver um campo a mais da erro na query. se analisar o resultado é assim que preciso.

select lanc.documento as Numero_Documento,
       lanc.data_operacao as Data_Operacao,
       (select hst.nome from tbl_historico hst where lanc.codhistorico = hst.codhistorico) as Historico_lancamento
from tbl_lancbancario lanc
where lanc.codcontabancaria = :paramConta and
lanc.data_operacao >= :paramDataI and
lanc.data_operacao <= :paramDataF

union all

select chq.numerocheque as Numero_Cheque,
       chq.emissao as Emissao,
       (select hst.nome from tbl_historico hst where chq.codhistorico = hst.codhistorico) as Historico_Cheque,
from tbl_cheque chq
where chq.codcontabancaria = :paramConta and
chq.emissao >= :paramDataI and
chq.emissao <= :paramDataF

union all

select dep.numeromovto as Numero_Deposito,
       dep.data_deposito as Depositado,
       (select hst.nome from tbl_historico hst where dep.codhistorico = hst.codhistorico) as Historico_Deposito
from tbl_deposito dep
where dep.codcontabancaria = :paramConta and
dep.data_deposito >= :paramDataI and
dep.data_deposito <= :paramDataF



[img]http://arquivo.devmedia.com.br/forum/imagem/196216-20160316-150947.png[/img]


O primeiro registro mostrado na imagem vem da tabela lancamentos e o ultimo registro da tabela deposito e os demais da tabela de cheques. é assim que preciso do resultado final, porem podera haver tabelas que tem mais ou menos campos a serem exibidos com isso o "union" da erro se algum dos selects tiver numero de campos diferentes a serem listados e ainda a questao de ordernar por data.
Responder

16/03/2016

Marcos P

Essa questão do union com colunas "a mais", você resolve colocando-as como NULL fixo e o nome padrão que você precisa.

Outra ideia é colocar tudo dentro de uma temporária e selecionar a partir dela...
Responder

16/03/2016

Iramar Junior

Essa questão do union com colunas "a mais", você resolve colocando-as como NULL fixo e o nome padrão que você precisa.

Outra ideia é colocar tudo dentro de uma temporária e selecionar a partir dela...


__________________________________________________________________________________________________________________________

como seria criar essa temporaria... ????
Responder

16/03/2016

Marcos P

Sobre a criação de temporárias, consulte a própria documentação do Firebird : http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html.

É o que o help chama de GTT...

A ideia seria criar essa estrutura para receber os registros que você precisa, em tempo de execução da consulta. Essa tabela seria populada com os registros obtidos em cada um das tabelas de origem e depois serviria de base para gerar o resultado final da pesquisa ( ou seja, diversos selects, fazendo insert nessa temporária ).

Repare que a ideia de gerar uma temporária é delegar ao banco de dados o controle do isolamento e tempo de vida dessa tabela, de modo que ela exista apenas durante a sessão de execução da query. Encerrada a sessão, essa tabela temporária, seria automaticamente eliminada no banco. Pelo menos é assim que funcionam temporárias em outros bancos "de verdade".

Como Firebird não é lá uma Brastemp, talvez você tenha de fazer esse controle em uma tabela fixa mesmo. Fica a seu critério.

De qualquer modo não entendi muito bem suas limitações em fazer isso com o LEFT JOIN ou UNION, conforme colocado acima. Se você quiser explorar melhor essas opções, vá no SqlFiddle cria a estrutura das tabelas e popule-as com alguns dados. O problema é que ele não tem Firebird, mas, eventualmente, podemos tentar algo com MySql.
Responder

17/03/2016

Iramar Junior

Sobre a criação de temporárias, consulte a própria documentação do Firebird : http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html.

É o que o help chama de GTT...

A ideia seria criar essa estrutura para receber os registros que você precisa, em tempo de execução da consulta. Essa tabela seria populada com os registros obtidos em cada um das tabelas de origem e depois serviria de base para gerar o resultado final da pesquisa ( ou seja, diversos selects, fazendo insert nessa temporária ).

Repare que a ideia de gerar uma temporária é delegar ao banco de dados o controle do isolamento e tempo de vida dessa tabela, de modo que ela exista apenas durante a sessão de execução da query. Encerrada a sessão, essa tabela temporária, seria automaticamente eliminada no banco. Pelo menos é assim que funcionam temporárias em outros bancos "de verdade".

Como Firebird não é lá uma Brastemp, talvez você tenha de fazer esse controle em uma tabela fixa mesmo. Fica a seu critério.

De qualquer modo não entendi muito bem suas limitações em fazer isso com o LEFT JOIN ou UNION, conforme colocado acima. Se você quiser explorar melhor essas opções, vá no SqlFiddle cria a estrutura das tabelas e popule-as com alguns dados. O problema é que ele não tem Firebird, mas, eventualmente, podemos tentar algo com MySql.


____________________________________________________________________________________________________________________________________________

Bom dia Marcos,

Feito alguns testes com aquela query usando union e esta filtrando td ok inclusive ordenando por codigo e data lancamento.

Talvez vc tenha uma outra opcao para resolver o levantamento de requisito necessario. é o seguinte, preciso de um sistema para controle de movimentacao financeira bancaria (sem caixa, somente movimentacoes bancarias), onde precisa ter movimento de entrada em contas (credito) e movimento de saida (debito), sendo que saida somente com emissao de cheque, nao trabalha com moeda (dinheiro). Tem que ter cadastro dos cheques para emissao em formulario continuo, sendo assim preciso ter controle dos cheques emitidos, nao emitidos, em transitos, cancelados e baixados (compensados). Preciso ter o controle das demais movimentacoes bancarias como deposito, aplicacao, resgate, rendimentos, transferencias de numerarios, etc.... Com todos esses dados necessito dos relatorios de movimentos dos cheques de acordo com sua situacao de acordo com que ja citei, alem das movimentacoes bancarias para emissao de extratos mensais, conciliacoes bancarias, consulta de saldo disponivel. Terei que ter um cadastro de favorecidos para cadastrar os cheques, visto que estes so sao emitidos nominais.

Essas sao os requisitos basicos, o cliente ate ja tem um sistema que esta fazendo parcialmente alguns desses requisitos, porem, quem desenvolveu deixou todas as movimentacoes em uma unica tabela, ou seja, misturou tudo.

De acordo com o exposto e sua experiencia (deu para notar que tem muita), como sugeria o DER para elaboracao do banco de dados. Agradeco
Responder

17/03/2016

Marcos P

Iramar,

O correto seria analisar isso com calma, para tentar determinar o melhor cenário.

E tempo, ultimamente, acabou se tornando um recurso escasso para mim.

Mas, como já comentei em outros posts, modelagem é um negócio muito pessoal e varia de acordo com vários fatores, como, por exemplo :

> O entendimento do cenário de uso
> Experiências anteriores em sistema similares
> Filosofia de modelagem
> O conhecimento prévio da tecnologia em que o sistema será implementado
> Restrições de negócio e/ou tecnologia

Não acredito muito que exista um modelo perfeito... existe um modelo implementável ao ambiente e suas restrições.

Todo modelo é passível de melhoria técnica, mas nem sempre as melhorias são justificáveis ou aplicáveis !
Responder