It's not liking the "as" ... this works more or less..
select b.amountowed, b.apr, b.dueday, b.minimumdue, b.payee,
b.payeeid, b.iscc, b.isactive, p2.payeeid, p2.amount,
case p2.amount when '' then 0 else sum(amount) end as amount,
case p2.payments when null then 0 else 1 end as paid,
payments
from bills b
left outer join (
select payeeid, sum(amount) amount, max(paiddate) paiddate, count(*)
payments
from payments p
where strftime("%m-%Y",paiddate) = '07-2008'
group by p.payeeid
) p2 on p2.payeeid = b.payeeid
where isactive = 0
group by amountowed, b.apr, b.dueday, b.minimumdue, b.payee,
b.payeeid, p2.amount, p2.paiddate,p2.payments, b.iscc, b.isactive
What is p2.payments if the count(*) was null?
I've tried
when null, when '', when ' '...
Is there a nvl() function or something like Oracle has?
Thanks
On Sat, Aug 30, 2008 at 5:45 PM, Greg Morphis <[EMAIL PROTECTED]> wrote:
> I have this SQL in Postgres and it works fine, I'm trying to port it
> over to SQLite and having issues.. SQLite doesnt like left outer
> joining to a subquery..
> Can you guys think of a way around this?
>
> select b.amountowed, b.apr, b.dueday, b.minimumdue, b.payee, p.note,
> b.payeeid, case when p.amount is null then 0 else sum(p.amount) end as
> amount, payments,
> case when p.amount is null then 0 else 1 end as paid, paiddate,
> b.iscc, b.isactive
> from bills b left outer join
> (
> select p.payeeid, sum(p.amount) as amount, max(paiddate) as paiddate,
> count(*) as payments, p.note
> from payments p
> where strftime("%m-%Y",paiddate) = '07-2008'
> group by p.payeeid, p.note
> ) p on p.payeeid = b.payeeid
> group by amountowed, b.apr, b.dueday, b.minimumdue, b.payee, p.note,
> b.payeeid, p.amount, p.paiddate,p.payments, b.iscc, b.isactive
>
> The subquery is used to get the null values so I can check to see if a
> bill is paid..
>
> Ideas?
>
> Thanks
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
