AnsweredAssumed Answered

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

Question asked by disabled_menno on Nov 10, 2010
Latest reply on May 31, 2011 by disabled_menno

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

Outcomes