1 Topic by apmuthu (edited by apmuthu 04/10/2015 04:48:35 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,171

Topic: Journal Inquiry NULL fix

In the Journal Inquiry page, the db_pager.inc seems to grey out (disable) the First Prev Next Last page links since it is unable to get the total number of records using COUNT(DISTINCT(CONCAT group_by_clause)). This is because, the a.gl_seq field is NULL in many records by design (thanks to an archaic MySQL 3 workaround for lack of subqueries - see includes/db/audit_trail_db.inc first function).

The following patch will fix it:

--- gl/includes/db/gl_db_trans.inc    Sat Apr 04 01:10:00 2015
+++ gl/includes/db/gl_db_trans.inc    Fri Apr 10 21:58:56 2015
@@ -637,7 +637,7 @@
     if (!$alsoclosed) {
         $sql .= " AND gl_seq=0";
     }
-    $sql .= " GROUP BY gl.tran_date, a.gl_seq, gl.type, gl.type_no";
+    $sql .= " GROUP BY gl.tran_date, IF(ISNULL(a.gl_seq),0,a.gl_seq), gl.type, gl.type_no";
     return $sql;
 }
 ?>
\ No newline at end of file

The IF(ISNULL(... construct can be replaced in all places in the file with COALESCE(a.gl_seq,0)

apmuthu's Website

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,171

Re: Journal Inquiry NULL fix

This issue has now been fixed in the db_pager.inc file itself using a subquery and hence above fix is not needed. It has been committed in the Official GitHub Mirror.

apmuthu's Website