Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 2 of 2 total
Thread SQL parsing error
Sun, Feb 10 2013 6:58 PMPermanent Link

Sean

Business Evolution Group

Hi,

We import data into a data warehouse from a legacy application that was previously using a Paradox database but has been upgraded to now use DBISAM..

We have updated the code to handle a few Syntax changes but I am now receiving the following error when trying to execute the SQL.

"ERROR [42000] [Elevate Software][DBISAM] DBISAM Engine Error # 11949 SQL parsing error - Expected end of statement but instead found . in SELECT SQL statement at line"

A snippet of SQL that is being executed follows.  There will be multiple subqueries unioned together in the full SQL.  But the following still results in the error.

"
SELECT T0.Company,
   T0.CommonReportingDate AS PostingDate,
   T0.JournalRemarks,
   T0.FormatCode,
   T0.CostCode AS Ref1,
   T0.Debit AS Debit,
   T0.Credit AS Credit,
   T0.ProfitCenter,
   T0.AutoVAT,
   SUM(T2.ExtendedHours) AS Quantity
FROM
(
   SELECT T0.CostCode, T0.CommonReportingDate, SUM(T0.Amount) AS Amount, SUM(T0.Amount) AS Debit, 0 AS Credit,
      SUBSTRING(T0.GLAccount, 1, 3) AS Company,
      SUBSTRING(T0.GLAccount, 5, 5) + SUBSTRING(T0.GLAccount, 11, 5) + SUBSTRING(T0.GLAccount, 17, 2) AS FormatCode,   
      SUBSTRING(T0.GLAccount, 20, 10) AS ProfitCenter,
      'Base' AS LineType,
      SUBSTRING(T1.Name, 11, 50) AS CompanyName,
      SUBSTRING(T1.Name, 11, 50) + ' Payroll' AS JournalRemarks,
      SUBSTRING(T1.Name, 11, 50) AS Ref1,
      'tNO' AS AutoVat
   FROM (ims.GLTrans T0 LEFT OUTER JOIN ims.PayPoint T1 ON SUBSTRING(T1.Name, 1, 3) = SUBSTRING(T0.GLAccount, 1, 3))   
   WHERE T0.Debit = 'D'
   GROUP BY T0.CostCode, T0.CommonReportingDate,
      SUBSTRING(T0.GLAccount, 1, 3),
      SUBSTRING(T0.GLAccount, 5, 5) + SUBSTRING(T0.GLAccount, 11, 5) + SUBSTRING(T0.GLAccount, 17, 2),
      SUBSTRING(T0.GLAccount, 20, 10),
      SUBSTRING(T1.Name, 11, 50),
      SUBSTRING(T1.Name, 11, 50) + ' Payroll',
      SUBSTRING(T1.Name, 11, 50)
   ) T0
   LEFT OUTER JOIN ims.TimesheetHistory  T2 ON (T2.PeriodDate = T0.CommonReportingDate) AND (T2.CostCentre = T0.CostCode)
WHERE T0.CommonReportingDate BETWEEN '20130101' AND '20130131'
GROUP BY
   T0.Company,
   T0.CommonReportingDate,
   T0.JournalRemarks,
   T0.FormatCode,   
   T0.CostCode,
   T0.Debit,
   T0.Credit,
   T0.ProfitCenter,
   T0.AutoVAT
"
Tue, Feb 12 2013 5:43 PMPermanent Link

Sean

Business Evolution Group

I now realise this is caused because Subqueries are not handled by DBISAM.
Would close the message if possible Smile
Image