Skip site navigation (1) Skip section navigation (2)

Re: aggregate function ?

From: Richard Broersma Jr <rabroersma(at)yahoo(dot)com>
To: coder(at)montx(dot)com, pgsql-novice(at)postgresql(dot)org
Subject: Re: aggregate function ?
Date: 2007-05-18 19:06:02
Message-ID: 387411.79656.qm@web31805.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-novice
I found the typo in the query: by the way, it seems my version of postgresql is more verbose with
it error message than yours.
...
ERROR:  missing FROM-clause entry in subquery for table "a1"
LINE 5:                         AND A1.data BETWEEN '2006-01-01' AND...
...

anyway here is the correction:

 
SELECT oid, concepte, deure, haver,
       delta_sum + ( SELECT SUM( COALESCE( A3.deure ) - COALESCE ( A3.haver ) )
                       FROM Assentaments A3
                      WHERE A3.clau_compte = '0257000000002'
                        AND A3.data BETWEEN '2006-01-01' AND '2006-06-30' ) AS starting_Sum
--This was A1 but should be A3^
  FROM( SELECT A1.oid, A1.concepte, A1.deure, A1.haver,
               sum( COALESCE( A2.deure, 0 ) -
                    COALESCE( A2.haver, 0 )) AS value_sum
          FROM Assentaments AS A1
    INNER JOIN Assentaments AS A2
            ON A1.oid >= A2.oid
           AND A1.numero = A2.numero
         WHERE A1.clau_compte = '0257000000002'
      GROUP BY A1.oid, A1.concepte, A1.deure, A1.haver
      ORDER BY A1.oid ) AS Summed_Assentaments( oid, concepte, deure, haver, delta_sum );

> 
> and here comes the error:
> 
> ERROR: missing FROM-clause entry in subquery for table "a1"
> estat SQL: 42P01
> Caràcter: 254
> 
> 
> I can send you in a private e-mail some rows in .txt if you want to try ...
> 
> And no, there's no relation between numero and compte in my table, I used numero because for me
> it was easier to use only one field that use one with to options, data and between, but the code
> should look like it's now.
> 
> Sorry for the inconvenience ...
> 
> 
> 
> thanks again !
> 
> 
> 
> 
> 
> 
> ----- Original Message -----
> From: Richard Broersma Jr
> [mailto:rabroersma(at)yahoo(dot)com]
> To: coder(at)montx(dot)com,
> pgsql-novice(at)postgresql(dot)org
> Sent: Fri, 18 May 2007 19:47:11 +0200
> Subject:
> Re: [NOVICE] aggregate function  ?
> 
> 
> > 
> > --- coder(at)montx(dot)com wrote:
> > > 
> > > ERROR: missing FROM-clause entry in subquery for table "a1"
> > > estat SQL: 42P01
> > > Caràcter: 254
> > > 
> > > 
> > > This line ' WHERE A1.numero=11189 ' must change as this:
> > > 
> > > 'WHERE A1.compte='572000001'  as the values must be from the same 'compte'
> > 
> > Yes, it sounds like there is a relationship in your datamodel that I am not
> > able to precieve
> > across emails.  If there is a relationship between compte and numero, you
> > should be able to
> > connect this relationship up in the SQL select query.
> > 
> > It is hard to know what the error is being produced by. can you send the
> > actual query that you are
> > posting and it resulting error message.
> > Regards,
> > Richard
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> >        subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> >        message can get through to the mailing list cleanly
> > 
> 


In response to

pgsql-novice by date

Next:From: coderDate: 2007-05-18 19:22:15
Subject: Re: [NOVICE] aggregate function ?
Previous:From: coderDate: 2007-05-18 18:11:35
Subject: Re: [NOVICE] aggregate function ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group