From ea00032210c8c3034ce02356a69166e52ef4872a Mon Sep 17 00:00:00 2001 From: Kyle M Hall Date: Mon, 13 Aug 2012 09:06:52 -0400 Subject: [PATCH] Bug 7887 - Followup - Make use of aqorders conditional. Use COALESCE to prevent NULL addition. COALESCE is an ANSI-compliant SQL function, which returns the first non-null value passed to it. I have verified it is available in both MySQL and Postgres. --- circ/reserveratios.pl | 7 ++++--- 1 files changed, 4 insertions(+), 3 deletions(-) diff --git a/circ/reserveratios.pl b/circ/reserveratios.pl index 4584cba..422ff6a 100755 --- a/circ/reserveratios.pl +++ b/circ/reserveratios.pl @@ -104,7 +104,8 @@ if ($order eq "biblio") { $sqlorderby = " ORDER BY reservecount DESC "; } -my $on_order_sql = ( $include_on_order_items ) ? "+ aqorders.quantity" : ""; +my $on_order_sql1 = ( $include_on_order_items ) ? "+ COALESCE(aqorders.quantity, 0)" : q{}; +my $on_order_sql2 = ( $include_on_order_items ) ? "LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber" : q{}; my $not_for_loan_comparison = ( $include_will_be_available ) ? "<=" : "="; my $strsth = @@ -128,11 +129,11 @@ my $strsth = biblio.title, biblio.author, count(DISTINCT reserves.borrowernumber) as reservecount, - count(DISTINCT items.itemnumber) $on_order_sql as itemcount + count(DISTINCT items.itemnumber) $on_order_sql1 as itemcount FROM reserves LEFT JOIN items ON items.biblionumber=reserves.biblionumber LEFT JOIN biblio ON reserves.biblionumber=biblio.biblionumber - LEFT JOIN aqorders ON reserves.biblionumber=aqorders.biblionumber + $on_order_sql2 WHERE notforloan $not_for_loan_comparison 0 AND damaged = 0 AND itemlost = 0 AND wthdrawn = 0 $sqldatewhere -- 1.7.2.5