sub condition_sql {
my( $class, $table, %opt ) = @_;
- # XXX: can be made faster with optimizations?
- # -remove some/all sub-selects?
- # -remove the two main separate selects?
-
- "0 = (select count(1) from cust_pkg
- where cust_pkg.no_auto = 'Y' and cust_pkg.pkgnum in
- (select distinct cust_bill_pkg.pkgnum
- from cust_bill_pkg, cust_pkg
- where cust_bill_pkg.pkgnum = cust_pkg.pkgnum
- and cust_bill_pkg.invnum = cust_bill.invnum
- and cust_bill_pkg.pkgnum > 0
- )
- )
- AND
- 0 = (select count(1) from part_pkg
- where part_pkg.no_auto = 'Y' and part_pkg.pkgpart in
- (select cust_pkg.pkgpart from cust_pkg
- where pkgnum in
- (select distinct cust_bill_pkg.pkgnum
- from cust_bill_pkg, cust_pkg
- where cust_bill_pkg.pkgnum = cust_pkg.pkgnum
- and cust_bill_pkg.invnum = cust_bill.invnum
- and cust_bill_pkg.pkgnum > 0
- )
- )
- )
+ # can be made still faster with optimizations?
+
+ "NOT EXISTS ( SELECT 1 FROM cust_pkg
+ LEFT JOIN part_pkg USING (pkgpart)
+ WHERE ( cust_pkg.no_auto = 'Y' OR part_pkg.no_auto = 'Y' )
+ AND cust_pkg.pkgnum IN
+ ( SELECT DISTINCT cust_bill_pkg.pkgnum
+ FROM cust_bill_pkg
+ WHERE cust_bill_pkg.invnum = cust_bill.invnum
+ AND cust_bill_pkg.pkgnum > 0
+ )
+ )
";
}