it seems that on the whole set there's something missing
1st query
indiban
anos
empresas
*indicadores*
2nd query
eleban
anos
empresas
*elementos*
moreover, in your "DAL syntax", only the references to those external
tables are taken.
The real missing link that could get you the answer is how to link
elementos and indicadores (or, more specifically, indicadores_id and
elementos_id). Without that, there's - apparently - no way to link eleban
and indiban.
On Saturday, November 5, 2016 at 3:16:10 PM UTC+1, Gualter Portella wrote:
>
> 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.
