Dear all,
I hope this e-mail finds you all well.
I have written two queries to retrieve data from my database (model). I did
them initially through plain SQL, then I followed the DAL syntax. They all
return the same output regardless the syntax chosen. Please, see the code
below:
Two different controllers:
def sql():
sql = "SELECT indiban.valor, meses.abrev, anos.numero, empresas.nome "
sql = sql + "FROM indiban INNER JOIN meses ON indiban.meses_id =
meses.id "
sql = sql + "INNER JOIN anos ON indiban.anos_id = anos.id "
sql = sql + "INNER JOIN indicadores ON indiban.indicadores_id =
indicadores.id "
sql = sql + "INNER JOIN empresas ON indiban.empresas_id = empresas.id "
sql = sql + "WHERE indicadores.id = 3 "
sql = sql + "AND meses.id = 5 AND anos.id = 22 Limit 10;"
ratios = db.executesql(sql)
sql = "SELECT eleban.valor, meses.abrev, anos.numero, empresas.nome "
sql = sql + "FROM eleban INNER JOIN meses ON eleban.meses_id = meses.id
"
sql = sql + "INNER JOIN anos ON eleban.anos_id = anos.id "
sql = sql + "INNER JOIN elementos ON eleban.elementos_id = elementos.id
"
sql = sql + "INNER JOIN empresas ON eleban.empresas_id = empresas.id "
sql = sql + "WHERE elementos.id = 5 "
sql = sql + "AND meses.id = 5 AND anos.id = 22 Limit 10;"
accounts = db.executesql(sql)
return locals()
def sfn():
ratios = db((Indiban.meses_id==mes_id)&(Indiban.anos_id==ano_id)&
(Indiban.indicadores_id==rspla_id)).select(Indiban.empresas_id,
Indiban.valor,
Indiban.meses_id, Indiban.anos_id, limitby=(0, 10))
accounts = db((Eleban.meses_id==mes_id)&(Eleban.anos_id==ano_id)&
(Eleban.elementos_id==ativo_id)).select(Eleban.empresas_id,
Eleban.valor,
Eleban.meses_id,
Eleban.anos_id, limitby=(0, 10))
return locals()
DAL output:
indiban.empresas_idindiban.
valorindiban.meses_idindiban.anos_idSISTEMA FINANCEIRO
NACIONAL0.1300052016BANCO DO BRASIL0.1100052016BRB - BANCO DE
BRASILIA0.0200052016POTTENCIAL-0.0100052016CAIXA ECONOMICA
FEDERAL0.0300052016BANCO INTERMEDIUM0.0400052016RIBEIRAO
PRETO0.1200052016BGN
CETELEM-0.1200052016SEMEAR-0.0100052016BM&FBOVESPA0.1500052016
------------------------------
eleban.empresas_ideleban.valoreleban.meses_ideleban.anos_idSISTEMA
FINANCEIRO NACIONAL7404931306106.43052016BANCO DO
BRASIL1070715292094.95052016BRB - BANCO DE
BRASILIA13013438306.52052016POTTENCIAL167724699.00052016CAIXA ECONOMICA
FEDERAL1096578802720.18052016BANCO INTERMEDIUM2745032207.17052016RIBEIRAO
PRETO486037290.78052016BGN
CETELEM8371996257.63052016SEMEAR717457527.11052016BM&FBOVESPA234811696.13052016
SQL Output:
((Decimal('0.1300'), u'mai', u'2016', u'SISTEMA FINANCEIRO NACIONAL'),
(Decimal('0.1100'), u'mai', u'2016', u'BANCO DO BRASIL'),
(Decimal('0.0200'), u'mai', u'2016', u'BRB - BANCO DE BRASILIA'),
(Decimal('-0.0100'), u'mai', u'2016', u'POTTENCIAL'), (Decimal('0.0300'),
u'mai', u'2016', u'CAIXA ECONOMICA FEDERAL'), (Decimal('0.0400'), u'mai',
u'2016', u'BANCO INTERMEDIUM'), (Decimal('0.1200'), u'mai', u'2016',
u'RIBEIRAO PRETO'), (Decimal('-0.1200'), u'mai', u'2016', u'BGN CETELEM'),
(Decimal('-0.0100'), u'mai', u'2016', u'SEMEAR'), (Decimal('0.1500'),
u'mai', u'2016', u'BM&FBOVESPA'))
------------------------------
((Decimal('7404931306106.43'), u'mai', u'2016', u'SISTEMA FINANCEIRO
NACIONAL'), (Decimal('1070715292094.95'), u'mai', u'2016', u'BANCO DO
BRASIL'), (Decimal('13013438306.52'), u'mai', u'2016', u'BRB - BANCO DE
BRASILIA'), (Decimal('167724699.00'), u'mai', u'2016', u'POTTENCIAL'),
(Decimal('1096578802720.18'), u'mai', u'2016', u'CAIXA ECONOMICA FEDERAL'),
(Decimal('2745032207.17'), u'mai', u'2016', u'BANCO INTERMEDIUM'),
(Decimal('486037290.78'), u'mai', u'2016', u'RIBEIRAO PRETO'),
(Decimal('8371996257.63'), u'mai', u'2016', u'BGN CETELEM'),
(Decimal('717457527.11'), u'mai', u'2016', u'SEMEAR'),
(Decimal('234811696.13'), u'mai', u'2016', u'BM&FBOVESPA'))
The data in the output are identical as expected. What I want to do is to
combine the two queries into one, preferably in DAL, so that I do not need
to use two loops in order to insert the indiban.valor and eleban.valor
values side by side in a view table. So far, I've failed miserably.
The tables indiban and eleban are linked to the meses, anos and empresas
tables as depicted in the relational schema below. If you can help me, I'd
appreciate much. Otherwise, I will stick to the two loops.
Best regards,[image: Imagem inline 2]
Gualter
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
