[freeside] last chance for MySQL support in 1.4.0

[Ron Gage]

On Sun, 2002-07-14 at 22:41, ivan wrote:
> 1.4.0 is ready to release.
> 
> If I don't receive patches from the MySQL-using community to work around
> MySQL's lack of subqueries, 1.4.0 will release without MySQL support.
> 
> See http://pouncequick.420.am/rt/Ticket/Display.html?id=438
> 
> You have one week.

I am not in a position to offer an exact patch, nor to test what I am
about to suggest, but if someone could give this a try...

SELECT @billpay:= COALESCE(SUM(amount),0) FROM cust_bill_pay WHERE
cust_bill_pay.invnum = cust_bill.invnum;
SELECT @creditpay:= COALESCE(SUM(amount),0) FROM cust_credit_bill WHERE
cust_credit_bill.invnum = cust_bill.invnum;
SELECT * FROM cust_bill WHERE (charged - @billpay - @creditpay) != 0;

By breaking the subselects out into individual selects and storing the
results in server side user variables, this should give you effectively
the same result as having a subselect.  The user variables appear to be
session specific so multiple people accessing the database at the same
time shouldn't step on each other.

It's kinda kludgy, I know, but it *SHOULD* work.  Yes, it would be nice
if MySQL actually had subselects available.


-- 
Ron Gage - Owner, Linux Network Services 
Reliable, Affordable, Secure Networking Solutions 
(989) 274-8088    Saginaw, Michigan