0 Replies Latest reply on Nov 10, 2010 5:31 AM by disabled_menno

    ODBC behaviour changed in FM9+ compared to FM6

      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"