martinthrelly wrote:

> hi i am trying to run the following sql within IBExpert but it wont
> run saying "invalid token Select". i realise this may be a simple
> error i am making but could somebody please point me out why i cannot
> join a summed amount like this. thanks.
>
> select
> v.vatcode,
> d.disbs,
> v.vatvalue,
> v.netvalue
> from vattransactions v
> inner join (
> select
> sum(netvalue) as disbs
> from disbursements) d on v.transno = d.transno
> where v.vatquarter = 20
> and v.inputoutput = 'O'
> order by
> v.vatcode
>

Hi,

Are you using FB 2.0 ? Derived tables are a new feature on FB 2.0 only.

Your sub-select (don't kow if it's the correct name for the derived
table select) does not have a group by, so it will return just on erow,
and in the derived table there is no column named "transno", I think you
should change it to something like:

select
v.vatcode,
d.disbs,
v.vatvalue,
v.netvalue
from vattransactions v
inner join (
select
sum(netvalue) as disbs, transno
from disbursements
group by transno) d on v.transno = d.transno
where v.vatquarter = 20
and v.inputoutput = 'O'
order by
v.vatcode



see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br