Thank you. I will link the tables and let you know the result. Best
Enviado do meu iPhone > Em 6 de nov de 2016, às 17:22, Niphlod <[email protected]> escreveu: > > 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, >> >> 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. -- 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.
