3 Replies Latest reply on May 31, 2011 6:32 AM by disabled_menno

    Valid and Working SQL-query's for a MSSQL-Server are refused by the ODBC-Import dialog

      Summary

      Valid and Working SQL-query's for a MSSQL-Server are refused by the ODBC-Import dialog

      Product

      FileMaker Pro

      Version

      9 thru 11 Standard and Advanced

      Operating system version

      Several Windows OS-versions

      Description of the issue

      A complex query that works fine from Excel, MS-Enterprise-manager and Filemaker Pro 6. Is refused as "Not valid SQL" by FM9+

      I have this query in a script in several FM6-solutions and there it works fine. The same query has today been tested in Excel and in MS-EM and there it works just fine as well. With these tests the exact same DSN has been used as the one used in FM9/10/11

      Steps to reproduce the problem

      Import record form ODBC-source
      Select DSN
      Enter username and password for the chosen DSN
      Klik "OK"
      Paste "query below" in SQL-query-field in Querybuilder for SQL-query's for "chosen DSN"
      Klik "OK"

      -- Query pasted
      SELECT "bt"."InvoiceNumber", "bt"."DebtorNumber" AS "OffsetNumber", SUM(ROUND(
      (CASE WHEN "bt"."AmountDC" > 0 AND "bt"."Type" = 'W' THEN "bt"."AmountDC" ELSE
            (CASE WHEN "bt"."Type" = 'S' AND "bt"."AmountDC" < 0 THEN -"bt"."AmountDC" ELSE NULL END )
      END ) , 2)) AS "Debit", SUM(ROUND(
      (CASE WHEN "bt"."AmountDC" < 0 AND "bt"."Type" = 'W' THEN -"bt"."AmountDC" ELSE
            (CASE WHEN "bt"."Type" = 'S' AND "bt"."AmountDC" > 0 THEN "bt"."AmountDC" ELSE NULL END )
      END ) , 2)) AS "Credit", SUM(ROUND((CASE WHEN "bt"."Type" = 'W' THEN "bt"."AmountDC" ELSE -"bt"."AmountDC" END ) , 2)) AS "AmountDC" FROM ((
             -- W Terms
             SELECT '' AS "Empty", "bt"."ID", "DebtorNumber", "CreditorNumber", "ValueDate", "AmountDC", "AmountTC",
                     "ProcessingDate", "InvoiceDate", "ReportingDate", "Type", "OffSetName", "PaymentType", "SupplierInvoiceNumber",
                     CAST("Description" AS VARCHAR(400)) AS "Description", "TransactionType", "OffsetReference",
                     "OffSetLedgerAccountNumber", "bt"."Blocked", "DocumentID", "OrderNumber", "InvoiceNumber",
                     "DueDate", "TcCode", "bt"."Status", "bt"."MatchID", "BatchNumber", "OwnBankAccount", "EntryNumber", "bt"."LedgerAccount",

                     "bt"."sysguid", (CASE WHEN "ExchangeRate" = 0 THEN ROUND( "ExchangeRate" ,6) ELSE ROUND(1/"ExchangeRate",6) END) AS "ExchangeRate",
                     "bt"."Approved", "bt"."Approved2"
             FROM "BankTransactions" "bt"
             LEFT OUTER JOIN (
                     SELECT "HS"."ID", "HS"."StatementType"
                     FROM "BankTransactions" "HS"
                     WHERE "HS"."Type" = 'S' AND "HS"."Status" = 'J'
                     AND "HS"."DebtorNumber" IS NOT NULL
                     AND ( 
                         (ISNULL("HS"."StatementType", '') <> 'F' AND ISNULL(ISNULL("HS"."InvoiceDate","HS"."ProcessingDate"),"HS"."valuedate") <= {d '2010-11-10'})
                         OR "HS"."StatementType" = 'F'))
             AS "HS" ON "bt"."MatchID" = "HS"."ID"
             LEFT OUTER JOIN (
                 SELECT "MatchID" FROM "BankTransactions" "W"
                     WHERE "W"."Type" = 'W' AND "W"."Status" <> 'V' AND "W"."MatchID" IS NOT NULL
                         AND "W"."DebtorNumber" IS NOT NULL
                     AND ISNULL(ISNULL("W"."InvoiceDate","W"."ProcessingDate"),"W"."ValueDate") > {d '2010-11-10'}
                     GROUP BY "MatchID"
             ) AS "HW" ON "bt"."MatchID" = "HW"."MatchID" AND "HS"."StatementType" = 'F'
             INNER JOIN "cicmpy" "ci" on "bt"."DebtorNumber" = "ci"."debnr"
             WHERE "Type" = 'W' AND "bt"."Status" IN ('C','A','P','J')  AND "EntryNumber" IS NOT NULL
             AND "DebtorNumber" IS NOT NULL AND ROUND("AmountDC",2) <> 0
             AND ISNULL(ISNULL("InvoiceDate","ProcessingDate"),"ValueDate") <= {d '2010-11-10'}
             AND ("HS"."ID" IS NULL OR ("HS"."ID" IS NOT NULL AND "HW"."MatchID" IS NOT NULL))

        UNION ALL
             -- S Terms
             SELECT '' AS "Empty", "s"."ID", "s"."DebtorNumber", "s"."CreditorNumber", "s"."ValueDate",        (ISNULL("s"."AmountDC",0) - ISNULL("W2"."AmountDC",0)) AS "AmountDC",        (ISNULL("s"."AmountTC",0) - ISNULL("W2"."AmountTC",0)) AS "AmountTC",
             "s"."ProcessingDate", "s"."InvoiceDate", "s"."ReportingDate", "s"."Type", "s"."OffSetName", "s"."PaymentType", "s"."SupplierInvoiceNumber", 
             CAST("s"."Description" AS VARCHAR(400)) AS "Description" , "s"."TransactionType", "s"."OffsetReference",
             "s"."OffSetLedgerAccountNumber", "s"."Blocked", "s"."DocumentID", "s"."OrderNumber",
             ISNULL("s"."InvoiceNumber", '') AS "InvoiceNumber"
             , "s"."DueDate", "s"."TCCode", "s"."Status" , "s"."MatchID", "s"."BatchNumber", "s"."OwnBankAccount", "s"."EntryNumber",  "s"."LedgerAccount",

             "s"."sysguid",  (CASE WHEN "ExchangeRate" = 0 THEN ROUND( "ExchangeRate" ,6) ELSE ROUND(1/"ExchangeRate",6) END) AS "ExchangeRate",
             "s"."Approved", "s"."Approved2"
             FROM "BankTransactions" "s"
             INNER JOIN "cicmpy" "ci" ON "s"."DebtorNumber" = "ci"."debnr"           LEFT OUTER JOIN
                     (SELECT "MatchID", ROUND(SUM(ROUND(ISNULL("AmountDC",0),2)),2) AS "AmountDC",
                     ROUND(SUM(ROUND(ISNULL("AmountTC",0),2)),2) AS "AmountTC"  FROM "BankTransactions" "w"
                     WHERE "w"."Type" = 'W' AND "w"."Status" IN ('C','A','P','J')
                     AND ISNULL("w"."InvoiceDate",ISNULL("w"."ProcessingDate","w"."ValueDate")) <= {d '2010-11-10'}
                     AND "w"."EntryNumber" IS NOT NULL

                     GROUP BY "MatchID"
                     HAVING "MatchID" IS NOT NULL ) AS "W2" ON "W2"."MatchID" = "S"."ID"
             WHERE "s"."Type"='S' AND "s"."Status" = 'J'
             AND "s"."DebtorNumber" IS NOT NULL
             AND ROUND("s"."AmountDC",2) <> 0
             AND ROUND((ISNULL("s"."AmountDC", 0) - ISNULL("W2"."AmountDC", 0)),2) <> 0

             AND "s"."ValueDate" <= {d '2010-11-10'}
      )) "bt"
      INNER JOIN "cicmpy" "ci" on "bt"."DebtorNumber"= "ci"."debnr" AND "bt"."DebtorNumber" IS NOT NULL
      LEFT OUTER JOIN "addresses" "addr" on "ci"."cmp_wwn" = "addr"."account" AND "addr"."Type" = 'INV'
      LEFT OUTER JOIN "classifications" "cc" ON "ci"."ClassificationId" = "cc"."ClassificationID"
      LEFT OUTER JOIN "cicntp" "cp" ON "cp"."cnt_id"="ci"."cnt_id"
      WHERE "ci"."debcode" IS NOT NULL
      AND ISNULL(ISNULL("bt"."InvoiceDate","bt"."ProcessingDate"),"bt"."Valuedate") <= {d '2010-11-10'}
      AND ("bt"."CreditorNumber" IS NULL OR "bt"."CreditorNumber" NOT IN ('  1683')) GROUP BY "bt"."InvoiceNumber", "ci"."debcode", "bt"."DebtorNumber", "ci"."cmp_name", "ci"."cmp_type", "ci"."cmp_status", "ci"."cmp_fctry", "ci"."creditline" ORDER BY "bt"."InvoiceNumber", "ci"."debcode"

      Expected result

      A list of 6 columns

      Actual result

      NO result because FM refuses to execute the query

      Exact text of any error message(s) that appear

      ODBC-Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '('.

      Workaround

      This exact same query works perfectly in a Filemaker 6 Setup

        • 1. Re: Valid and Working SQL-query's for a MSSQL-Server are refused by the ODBC-Import dialog

          Any of the TS people care to answer to this issue?

          For now I have a workaround: I have created views in the enterprise-manager, one for each database... However one needs access to the EM and that is not the default. So I still would like to be able to do this from FM.

          • 2. Re: Valid and Working SQL-query's for a MSSQL-Server are refused by the ODBC-Import dialog
            TSGal

            menno:

            Thank you for your post, and I apologize for the late reply.

            First, there were many changes since the FileMaker Pro 6 timeframe.  Also, there was an issue with FileMaker Pro 10 not being able to send an SQL query longer than 4096 characters, but if you are getting this in both FileMaker Pro 9 and FileMaker Pro 11, there is something else occurring.

            Have you tried splitting the query into smaller parts for testing purposes to hopefully narrow down where the problem lies.  For example, first try:

            SELECT "bt"."InvoiceNumber" FROM BankTransacations

            If that works, then expand to:

            SELECT "bt"."InvoiceNumber", "bt"."DebtorNumber" FROM BankTransactions

            If neither work, try replacing the double quotes with single quotes.

            Continue to expand the SQL statement, as this may provide a better idea what is causing it to fail.

            TSGal
            FileMaker, Inc.

            • 3. Re: Valid and Working SQL-query's for a MSSQL-Server are refused by the ODBC-Import dialog

              TSGal:

              Thank you for the reply. The query in my first post is actually larger than 4096 characters and i believe that that is causing the refusal from Filemaker Pro to accept this statement as valid SQL.

              If I use this exact statement in Filemaker 6 .... it works just fine, so I would expect it to just function fine fine in FM7-11 as well, but it does not???

              Rewriting this query is not an options since this query already is optimized.

              For now I have created views in the MSSQL-enterpise-manager and now I query the views from Filemaker. BTW: the views are using the QUERY as in my first post, on the same computer as where FMServer is running, using the very same DSN as I've tried to use from Filemaker. That seems to imply to me that "ODBC-Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '('" is caused by Filemaker Pro itself changing the query's ... quotes .. spaces .. data .. whatever ?

              I suspect (without proper inside knowledge) the real problem is here that Filemaker Pro IS somehow VALIDATING the statement BEFORE even SENDING it to the DSN and instead it should just send the query and wait for an error from either the ODBC-connector or the DBM to which it connects? .... You send a query and all you get in return is either a table or an errormessage, shouldn't it be that way?

              regards, Menno