• Registered: 03/31/2011
  • Posts: 15

Topic: Supplier transaction inquiry - error 1064

I don't know whether this is an install error or a bug but since this is a clean install with no data I am posting it here.  As I say clean install, no data but I get this error.  Have tried setting up new db and reinstalling and have also tried with data but always same problem - Everything else is fine and I think the whole product looks very good.  Our server is running PHP 5.2 and MySql 4.1.11 - I know this is a fairly old version but have checked with host and all requirements are met, so should not be a problem.  Any help appreciated.  The full message is:

DATABASE ERROR : Error reading record set
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')) as tr)' at line 32
sql that failed was : SELECT COUNT(*) FROM ((SELECT trans.type, trans.trans_no, trans.reference, supplier.supp_name, trans.supp_reference, trans.tran_date, trans.due_date, supplier.curr_code, (trans.ov_amount + trans.ov_gst + trans.ov_discount) AS TotalAmount, trans.alloc AS Allocated, ((trans.type = 20 OR trans.type = 21) AND trans.due_date < '2011-04-01') AS OverDue, (ABS(trans.ov_amount + trans.ov_gst + trans.ov_discount - trans.alloc) <= 0.005) AS Settled FROM 0_supp_trans as trans, 0_suppliers as supplier WHERE (supplier.supplier_id = trans.supplier_id AND trans.tran_date >= '2011-03-02' AND trans.tran_date <= '2011-04-01' AND trans.ov_amount != 0) UNION (SELECT 25 as type, trans.id as trans_no, trans.reference, supplier.supp_name, '' as supp_reference, delivery_date as tran_date, '' as due_date, supplier.curr_code, '' AS TotalAmount, '' AS Allocated, 0 as OverDue, 1 as Settled FROM 0_grn_batch as trans, 0_suppliers as supplier WHERE supplier.supplier_id = trans.supplier_id AND trans.delivery_date >= '2011-03-02' AND trans.delivery_date <= '2011-04-01')) as tr)

  • Registered: 12/25/2007
  • Posts: 2,448

Re: Supplier transaction inquiry - error 1064

Seems this query is not compatible with MySQL 4 (works right under 5.1). If you can, please try to locate the problem testing the displayed query in phpmyadmin.

Janusz

  • Registered: 03/31/2011
  • Posts: 15

Re: Supplier transaction inquiry - error 1064

Thanks for quick response - tried as you suggested and just came up with same error message so must be a compatibility problem.  We are going to solve this by moving servers because there may be other issues as well that we haven't discovered - Have already talked to our hosts to get it organised.

Many thanks again