[Evan White]

In the past we have used the CASE statement for an excel VB Query using a third party ODBC link.

Using the new ODBC driver we are unable to use the CASE statement.

This is the query that I am trying to change but am having no luck.

Any help would be greatly appreciated.

” SELECT TRANSACTIONS_1.TRANSACTION_TYPE AS Transaction_Type, TRANSACTIONS_1.TRANDATE AS Transaction_Date, TRANSACTIONS_1.TRANID AS Transaction_No, ENTITY_1.FULL_NAME AS Supplier, TRANSACTION_LINES_1.MEMO AS Memo, NULL AS Billed, CASE WHEN TRANSACTIONS_1.TRANSACTION_TYPE = ‘Bill Credit’ THEN TRANSACTION_LINES_1.AMOUNT ELSE -TRANSACTION_LINES_1.AMOUNT END AS Paid, TRANSACTIONS_1.TRANDATE AS Date_Paid, ENTITY_1.EMAIL, ENTITY_1.ADDRESS_ONE, ENTITY_1.ADDRESS_TWO, ENTITY_1.CITY, ENTITY_1.STATE, ENTITY_1.ZIPCODE AS Postcode, ENTITY_1.EFT as EFT ” _

& ” FROM TRANSACTIONS TRANSACTIONS_1, TRANSACTION_LINES TRANSACTION_LINES_1, ENTITY ENTITY_1 ” _

& ” WHERE (TRANSACTIONS_1.TRANDATE =?) AND TRANSACTIONS_1.STATUS ‘Voided’ AND TRANSACTIONS_1.TRANSACTION_TYPE IN (‘Bill Payment’, ‘Expense Report’) AND TRANSACTION_LINES_1.TRANSACTION_ID = TRANSACTIONS_1.TRANSACTION_ID AND TRANSACTIONS_1.ENTITY_ID = ENTITY_1.ENTITY_ID AND TRANSACTION_LINES_1.TRANSACTION_LINE_ID = 0″ _

& “UNION ” _

& “SELECT TRANSACTIONS_1.TRANSACTION_TYPE AS Transaction_Type, TRANSACTIONS_1.TRANDATE AS Transaction_Date, TRANSACTIONS_1.TRANID AS Transaction_No, ENTITY_1.FULL_NAME AS Supplier, TRANSACTION_LINES_1.MEMO AS Memo, -TRANSACTION_LINES_1.AMOUNT AS Billed, NULL AS Paid, TRANSACTIONS_2.TRANDATE AS Date_Paid, ENTITY_1.EMAIL, ENTITY_1.ADDRESS_ONE, ENTITY_1.ADDRESS_TWO, ENTITY_1.CITY, ENTITY_1.STATE, ENTITY_1.ZIPCODE AS Postcode, ENTITY_1.EFT as EFT ” _

& “FROM TRANSACTIONS TRANSACTIONS_2,TRANSACTIONS TRANSACTIONS_1, TRANSACTION_LINKS TRANSACTION_LINKS_1, ENTITY ENTITY_1,TRANSACTION_LINES TRANSACTION_LINES_1 ” _

& “WHERE (TRANSACTIONS_2.TRANDATE =?) AND TRANSACTIONS_2.STATUS ‘Voided’ AND TRANSACTIONS_2.TRANSACTION_TYPE IN (‘Bill Payment’) AND TRANSACTION_LINKS_1.APPLIED_TRANSACTION_ID = TRANSACTIONS_2.TRANSACTION_ID AND TRANSACTIONS_1.TRANSACTION_ID = TRANSACTION_LINKS_1.ORIGINAL_TRANSACTION_ID AND ENTITY_1.ENTITY_ID = TRANSACTIONS_1.ENTITY_ID AND TRANSACTION_LINES_1.TRANSACTION_ID = TRANSACTIONS_1.TRANSACTION_ID AND TRANSACTION_LINES_1.TRANSACTION_LINE_ID = 0 ” _

& “UNION ” _

& “SELECT TRANSACTIONS_3.TRANSACTION_TYPE AS Transaction_Type, TRANSACTIONS_3.TRANDATE AS Transaction_Date, TRANSACTIONS_3.TRANID AS Transaction_No, ENTITY_2.FULL_NAME AS Supplier, TRANSACTION_LINES_2.MEMO AS Memo, ” _

& “CASE WHEN TRANSACTIONS_3.TRANSACTION_TYPE ‘Bill Credit’ THEN -TRANSACTION_LINES_2.AMOUNT ELSE NULL END AS Billed, CASE WHEN TRANSACTIONS_3.TRANSACTION_TYPE = ‘Bill Credit’ THEN TRANSACTION_LINES_2.AMOUNT ELSE NULL END AS Paid, ” _

& “TRANSACTIONS_4.TRANDATE AS Date_Paid, ENTITY_2.EMAIL, ENTITY_2.ADDRESS_ONE, ENTITY_2.ADDRESS_TWO, ENTITY_2.CITY, ENTITY_2.STATE, ENTITY_2.ZIPCODE AS Postcode, ENTITY_2.EFT as EFT ” _

& “FROM TRANSACTIONS TRANSACTIONS_4, TRANSACTIONS TRANSACTIONS_3, TRANSACTION_LINKS TRANSACTION_LINKS_1, TRANSACTION_LINKS TRANSACTION_LINKS_2, ENTITY ENTITY_2, TRANSACTION_LINES TRANSACTION_LINES_2 ” _

& “WHERE (TRANSACTIONS_4.TRANDATE =?) AND TRANSACTIONS_4.STATUS ‘Voided’ AND TRANSACTIONS_4.TRANSACTION_TYPE = ‘Bill Payment’ AND TRANSACTIONS_3.TRANSACTION_TYPE = ‘Bill Credit’ AND TRANSACTION_LINKS_1.APPLIED_TRANSACTION_ID = TRANSACTIONS_4.TRANSACTION_ID AND TRANSACTION_LINKS_2.ORIGINAL_TRANSACTION_ID = TRANSACTION_LINKS_1.ORIGINAL_TRANSACTION_ID AND TRANSACTIONS_3.TRANSACTION_ID = TRANSACTION_LINKS_2.APPLIED_TRANSACTION_ID AND ENTITY_2.ENTITY_ID = TRANSACTIONS_3.ENTITY_ID AND TRANSACTION_LINES_2.TRANSACTION_ID = TRANSACTIONS_3.TRANSACTION_ID AND TRANSACTIONS_4.TRANSACTION_ID TRANSACTIONS_3.TRANSACTION_ID AND TRANSACTION_LINES_2.TRANSACTION_LINE_ID = 0 ” _

& “UNION ” _

& “SELECT TRANSACTIONS_3.TRANSACTION_TYPE AS Transaction_Type, TRANSACTIONS_3.TRANDATE AS Transaction_Date, TRANSACTIONS_3.TRANID AS Transaction_No, ENTITY_2.FULL_NAME AS Supplier, TRANSACTION_LINES_2.MEMO AS Memo,” _

& “CASE WHEN TRANSACTIONS_3.TRANSACTION_TYPE ‘Bill Credit’ THEN -TRANSACTION_LINES_2.AMOUNT ELSE NULL END AS Billed, CASE WHEN TRANSACTIONS_3.TRANSACTION_TYPE = ‘Bill Credit’ THEN TRANSACTION_LINES_2.AMOUNT ELSE NULL END AS Paid, ” _

& “TRANSACTIONS_6.TRANDATE AS Date_Paid, ENTITY_2.EMAIL, ENTITY_2.ADDRESS_ONE, ENTITY_2.ADDRESS_TWO, ENTITY_2.CITY, ENTITY_2.STATE, ENTITY_2.ZIPCODE AS Postcode, ENTITY_2.EFT AS EFT ” _

& “FROM TRANSACTIONS TRANSACTIONS_6, TRANSACTIONS TRANSACTIONS_3, transactions transactions_5, TRANSACTION_LINKS TRANSACTION_LINKS_1, TRANSACTION_LINKS TRANSACTION_LINKS_2, TRANSACTION_LINKS TRANSACTION_LINKS_3, ENTITY ENTITY_2, TRANSACTION_LINES TRANSACTION_LINES_2 ” _

& “WHERE (TRANSACTIONS_6.TRANDATE =?) AND TRANSACTIONS_6.STATUS ‘Voided’ AND TRANSACTIONS_6.TRANSACTION_TYPE = ‘Bill Payment’ AND TRANSACTIONS_5.TRANSACTION_TYPE = ‘Bill Credit’ AND TRANSACTION_LINKS_1.APPLIED_TRANSACTION_ID = TRANSACTIONS_6.TRANSACTION_ID AND TRANSACTION_LINKS_2.ORIGINAL_TRANSACTION_ID = TRANSACTION_LINKS_1.ORIGINAL_TRANSACTION_ID And TRANSACTION_LINKS_3.applied_transaction_id = transaction_links_2.applied_transaction_id And transactions_5.TRANSACTION_ID = transaction_links_2.applied_transaction_id And TRANSACTIONS_3.TRANSACTION_ID = TRANSACTION_LINKS_3.ORIGINAL_TRANSACTION_ID And ENTITY_2.ENTITY_ID = TRANSACTIONS_3.ENTITY_ID ” _

& “AND TRANSACTION_LINES_2.TRANSACTION_ID = TRANSACTIONS_3.TRANSACTION_ID AND TRANSACTIONS_6.TRANSACTION_ID TRANSACTIONS_3.TRANSACTION_ID AND TRANSACTION_LINES_2.TRANSACTION_LINE_ID = 0 ” _

& “ORDER BY SUPPLIER, TRANSACTION_NO “
This is a cached copy. Click here to see the original post.

[mhuffman]

This is a known issue with the current version of the ODBC driver. This has been addressed by our ODBC vendor and we plan to roll out a new version of the driver this spring that will fix this and several other SQL92 syntax issues.

[carol]

RE: CASE Statement

So, this means–what? Everybody just has to wait until NS gets around to fixing it? If it’s a known issue, why hasn’t it been fixed already?

[mhuffman]

The vendor we license the ODBC driver from has resolved the issue, but it requires installation of the new version of the driver (and on our end, server.) Since we have to run both versions in parallel for a while so users can update, this is a rather involved release process, but we are wrapping up testing for release around the same time as the 2010.1 major release.

[Author]

Posts