[freeside] Error in cust_main.cgi

[Donald Greer]

    MySQL Users:
    If somebody has FS 1.4.0 setup on MySQL (or is interested in setting 
it up to test this), please give the following a try.  I'm attempting to 
use the examples in the MySQL docs to translate the Select-Select stuff 
that Ivan uses to MySQL.
    Two caveats: I don't have FS setup at the moment and I'm not an SQL
wizard.
    If somebody with MySQL and an experimental FS system would check this
out and fix whatever I've probably broken and submit it to Ivan, I'd
really appreciate it ... I don't want to have to manage anymore RDBMSs
than I have to. ;^)
   These may be completely off base, but hopefully they'll give somebody 
a starting point :^).  You could use a different table type, but if you 
have the memory, "HEAP" is definately the fastest and most closely 
emulates the SELECT-SELECT behaviour of other systems.
   Ivan: FYI, the SELECT-SELECT functionality is on the devel schedule 
for MySQL 4.x.  Hopefully that will resolve the last big issue for FS on 
MySQL.
   Don


  From "http://www.sisd.com/freeside/list-archive/msg02482.html":
  > SELECT * FROM cust_main
  >      WHERE 0 < ( SELECT COUNT(*) FROM cust_pkg
  >                    WHERE cust_pkg.custnum = cust_main.custnum
  >                      AND ( cust_pkg.cancel IS NULL
  >                            OR cust_pkg.cancel = 0
  >                          )
  >                )
  >         OR 0 = ( SELECT COUNT(*) FROM cust_pkg
  >                    WHERE cust_pkg.custnum = cust_main.custnum
  >                )
  >
Becomes:
--------------------
CREATE TEMPORARY TABLE temp1 TYPE=HEAP
	SELECT cust_pkg.custnum,COUNT(*) as count
	FROM cust_pkg,cust_main
	WHERE cust_pkg.custnum = cust_main.custnum
	AND ( cust_pkg.cancel IS NULL
	OR cust_pkg.cancel = 0
	);
CREATE TEMPORARY TABLE temp2 TYPE=HEAP
	SELECT cust_pkg.custnum,COUNT(*) as count
	FROM cust_pkg,cust_main
	WHERE cust_pkg.custnum = cust_main.custnum;
SELECT cust_man.*
	FROM cust_main, temp1, temp2
	WHERE temp1.custnum = cust_main.custnum
	AND temp2.custnum = cust_main.custnum
	AND (temp1.count > 0
	OR temp2.count = 0 );
DROP TABLE temp1,temp2;


--------------------
  >SELECT * FROM cust_pkg
  >    WHERE 0 <
  >    ( SELECT count(*) FROM pkg_svc
  >          WHERE pkg_svc.pkgpart = cust_pkg.pkgpart
  >            AND pkg_svc.quantity > ( SELECT count(*) FROM cust_svc
  >                                       WHERE cust_svc.pkgnum =
  >                                             cust_pkg.pkgnum
  >                                         AND cust_svc.svcpart =
  >                                             pkg_svc.svcpart
  >                                   )
  >      )
  >
Becomes:
--------------------
CREATE TEMPORARY TABLE temp1 TYPE=HEAP
	SELECT cust_svc.pkg_num,cust_svc.svcpart,COUNT(*) as count
	FROM cust_pkg,cust_svc,pkg_svc
	WHERE cust_pkg.pkgnum = cust_svc.pkgnum
	AND cust_svc.svcpart = pkg_svc.svcpart;
CREATE TEMPORARY TABLE temp2 TYPE=HEAP
	SELECT cust_pkg.pkgpart,COUNT(*) as count
	FROM temp1,pkg_svc,cust_pkg
	WHERE cust_pkg.pkgpart = pkg_svc.pkgpart
	AND cust_pkg.pkgnum = temp1.pkg_num
	AND pkg_svc.svcpart = temp1.svcpart
	AND pkg_svc.quantity > temp1.count;
SELECT cust_pkg.*
	FROM cust_pkg, temp2
	WHERE cust_pkg.pkgpart = temp2.pkgpart
	AND 0 < temp2.count;
DROP TABLE temp1,temp2;


  >SELECT * FROM svc_acct
  >    WHERE 0 <
  >    ( SELECT count(*) FROM cust_svc
  >          WHERE cust_svc.svcnum = svc_acct.svcnum
  >          AND pkgnum IS NULL
  >    )
  >
CREATE TEMPORARY TABLE temp1 TYPE=HEAP
	SELECT cust_svc.svcnum,COUNT(*) as count
	FROM cust_svc,svc_acct
	WHERE cust_svc.svcnum = svc_acct.svcnum
	AND cust_svc.pkgnum IS NULL;
SELECT svc_acct.*
	FROM svc_acct, temp2
	WHERE svc_acct.svcnum = temp2.svcnum
	AND 0 < temp2.count;
DROP TABLE temp1;






-- 
--------------------------------------------------------
Donald L. Greer, Jr                  dgreer at AustinTX.COM
System Administrator                 Voice: 512-300-0176
AustinTX                        http://www.AustinTX.COM/
    All opinions are my own.  Flame me directly.

"I don't necessarily believe software should be free...
but if you pay for it, it should work!" -- Me