Consulas e subconsultas em varias tabelas
15/03/2016
0
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
Iramar Junior
Curtir tópico
+ 0
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.
É 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.
Marcos P
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.
É 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
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 :
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.
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 :
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.
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.
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.
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.
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...
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...
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.
É 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.
É 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 !
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
Clique aqui para fazer login e interagir na Comunidade :)
