AnsweredAssumed Answered

ODBC behaviour changed in FM9+ compared to FM6

Question asked by disabled_menno on Nov 10, 2010

Title

ODBC behaviour changed in FM9+ compared to FM6

Post

Below you'll find a query in an MSSQL-database that works perfectly in FMP-6 using a DSN. The same query works fine in Excel and in MSSQL-Enterprisemanager, but I need the result in Filemaker.

This query however does not work using the same setup in FM9, FM10 and FM11? Has anyone encountered the same problem?

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"

Outcomes