Re: Error with Subquery and Group By

Adalberto Baldini wrote:

> Which is the conflict ?

	When you (1) have an aggregate and (2) do a group by, you *must*
include all non-aggregate fields in the GROUP BY. But you don't include
the subquery in that (nor can you), so the GROUP BY is invalid.

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:

With following query after added last column subquery, I get error :
Invalid token.
Dynamic SQL Error.
SQL error code = -104.
invalid column reference.


select a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio, a.o_curcytot,
  a.o_curcynaz,   max(a.o_percam),
  sum(a.o_prnzdr) - sum(a.o_prnzav) Sdo_Naz,
  sum(a.o_prexdr), sum(a.o_prexav),
  sum(a.o_prnzdr), sum(a.o_prnzav),
  (select b1.nicknm from sezio b1 where b1.csoc = a.o_csoc and
   b1.nreserc = a.o_nreserc  and b1.csez = a.o_sezio and
   b1.fdel = '0' ) as DescrSezio
  from SDICOGE (:csoc ,: abinp , :fil , :lastdata , :allambfil ) a
  group by  a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio,
            a.o_curcytot, a.o_curcynaz
  order by  a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio, a.o_curcytot

If I remove sum fields and group by clause query works.
If I remove subquery , query works.

Which is the conflict ?

Thanks

Adalberto Baldini wrote:

> are you saying it is not possible ? 

	Yes.

> In this case what could be a solution ?

	You *may* be able to put the query with the subquery in a VIEW and
group that. I haven't tried it. You could certainly put it in a
SELECTable proc.

-- 
Craig Stuntz [TeamB] · Vertex Systems Corp. · Columbus, OH
  Delphi/InterBase Weblog : http://blogs.teamb.com/craigstuntz
Everything You Need to Know About InterBase Character Sets:

Only to understand you reply :

Craig Stuntz [TeamB] wrote:

> 
> 	When you (1) have an aggregate 
any max, sum column ?

and (2) do a group by, you *must*
> include all non-aggregate fields in the GROUP BY. 

But you don't include the subquery in that
I tried to do it referring to this column with an alias name or with the 
column number but it doesn't work.

  (nor can you),
  are you saying it is not possible ? In this case what could be a 
solution ?

Thanks

Hi,

> select a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio, a.o_curcytot,
>  a.o_curcynaz,   max(a.o_percam),
>  sum(a.o_prnzdr) - sum(a.o_prnzav) Sdo_Naz,
>  sum(a.o_prexdr), sum(a.o_prexav),
>  sum(a.o_prnzdr), sum(a.o_prnzav),
>  (select b1.nicknm from sezio b1 where b1.csoc = a.o_csoc and
>   b1.nreserc = a.o_nreserc  and b1.csez = a.o_sezio and
>   b1.fdel = '0' ) as DescrSezio
>  from SDICOGE (:csoc ,: abinp , :fil , :lastdata , :allambfil ) a
>  group by  a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio,
>            a.o_curcytot, a.o_curcynaz
>  order by  a.o_csoc, a.o_nreserc, a.o_conto, a.o_sezio, a.o_curcytot
>
> If I remove sum fields and group by clause query works.
> If I remove subquery , query works.
>
> Which is the conflict ?

Your query looks fine, but for some reason InterBase seems not to be able to
handle it.
There are some workarounds you can try
- put an aggregate function around the sub-select (MIN / MAX)
- put an aggregate function around b1.nicknm (MIN / MAX)
- move the sub-select into the from clause as an LEFT JOIN and group on
b1.nicknm too

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database with many powerful SQL features:
  http://www.firebirdsql.org
  http://www.firebirdsql.info

General database developer support:
  http://www.databasedevelopmentforum.com

Support list for Interbase and Firebird users:
  firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep:
  news://newsgroups.firebirdsql.info