CODEWITHSUNDEEP
sqlfirebird
Networx Tim
Networx Tim

Reputation: 13

join and group by query works in firebird 2.1 but not in 1.5

I have the following query that works fine in firebird 2.1, however I cannot get it to work on a db with the exact same structure in 1.5

select c.printchecknumber, v.voidamount 
from checks c 
join (select checknumber, sum(voidamount) as voidamount 
      from checkitem 
      where voidtype =1 
      group by checknumber) v on c.checknumber = v.checknumber 
order by c.printchecknumber

Any ideas?

The error message is Invalid token Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 61 the error is at the start of the second select

Upvotes: 1

Views: 1684

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270301

I'm guessing that Firebird 1.5 doesn't support subqueries in the from clause. In any case, you can write this as a simpler query. The following should do what you want:

select c.printchecknumber, sum(voidamount) as voidamount 
from checkitem ci join
     checks c
     on ci.checknumber = c.checknumber
where ci.voidtype =1 
group by c.printchecknumber;

EDIT:

If you want to include checkid, then this might work:

select c.printchecknumber, c.checkid, sum(voidamount) as voidamount 
from checkitem ci join
     checks c
     on ci.checknumber = c.checknumber
where ci.voidtype =1 
group by c.printchecknumber, c.checkid;

Upvotes: 3

Hogan
Hogan

Reputation: 70529

I don't know why it does not work on an earlier system and I'd need to know the error message to help, but the following would work on any sql which supports the over() clause which I believe firebird does with 3.0

select c.printchecknumber,
       sum(v.voidamount) over (partition by printchecknumber)
from checks c
join checkitem v on c.checknumber = v.checknumber and v.voidtype = 1

Upvotes: 1

Related Questions