-
1. Re: Working Firebird query produces error in Tableau Custom SQL
Kaz Shakir Mar 31, 2017 8:11 AM (in response to mark.welsh)I have no experience with Firebird, but I have had a similar issue occur with an Oracle database. It turned out the issue was that our version of Oracle did not support lowercase letters for the names of columns, and in the Custom SQL I was referring to column the same way it's displayed in Tableau (first letter capital, and all other letters lower case), and that did not work. When I changed the column name in the custom sql to all caps, it fixed the problem.
-
2. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Mar 31, 2017 8:36 AM (in response to Kaz Shakir)Thanks for the tip Kaz. I just tried changing from all caps to all lowercase and then to capitalize each word, unfortunately nothing changed. I suppose I can rule that out.
-
3. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Apr 20, 2017 2:46 PM (in response to mark.welsh)*
-
4. Re: Working Firebird query produces error in Tableau Custom SQL
Tom WApr 20, 2017 2:52 PM (in response to mark.welsh)
Hi Mark,
I'd suggest you create a case with Tableau Support for this.
However it may help to post your SQL in here and a screenshot of the exact error as well as the 'show details' button if that appears on the dialog.
-
5. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Apr 20, 2017 3:21 PM (in response to Tom W)Hi Tom,
Thanks for the reply.
The Show Details of the error is "Firebird database error 335544396: column is not defined in table".
I tried cleaning up the table aliases and removing unnecessary syntax here and there, but that didn't work. The SQL runs fine in FlameRobin.
Here's the SQL:
SELECT ARCHIVE_TRANSACTIONS_1.INS_CARRIER_CODE,
ARCHIVE_TRANSACTIONS_1.PATNO,
REGISTER_1.LNAME, REGISTER_1.FNAME,
REGISTER_1.BIRTHDAT,
ARCHIVE_TRANSACTIONS_1.INSURED_ID,
ARCHIVE_TRANSACTIONS_1.DATE1,
ARCHIVE_TRANSACTIONS_1.UPDATED1,
ARCHIVE_TRANSACTIONS_1.TRNSXNO,
ARCHIVE_TRANSACTIONS_1.CPTCODE,
ARCHIVE_TRANSACTIONS_1.CPT_DESCRIPTION,
ARCHIVE_TRANSACTIONS_1.DRINIT,
ARCHIVE_TRANSACTIONS_1.CHARGE,
ARCHIVE_TRANSACTIONS_1.TX_BALANCE,
ARCHIVE_TRANSACTIONS_1.COPAYBALANCE,
ARCHIVE_TRANSACTIONS_1.MEMO1,
REGISTER_1.REFERDBY,
ARCHIVE_TRANSACTIONS_1.INVOICENO,
REGISTER_1.HPHONE, REGISTER_1.BPHONE,
ARCHIVE_TRANSACTIONS_1.REND_ADDR_ID,
DRLABEL_1.FNAME FNAME_2,
DRLABEL_1.LNAME LNAME_2,
INS_CARRIER_1.INS_CARRIER_NAME
FROM ARCHIVE_TRANSACTIONS ARCHIVE_TRANSACTIONS_1
LEFT OUTER JOIN REGISTER REGISTER_1 ON
(REGISTER_1.PATNO = ARCHIVE_TRANSACTIONS_1.PATNO)
LEFT OUTER JOIN DRLABEL DRLABEL_1 ON
(DRLABEL_1.ADDR_ID = ARCHIVE_TRANSACTIONS_1.REND_ADDR_ID)
LEFT OUTER JOIN INS_CARRIER INS_CARRIER_1 ON
(INS_CARRIER_1.INS_CARRIER_CODE = ARCHIVE_TRANSACTIONS_1.INS_CARRIER_CODE)
WHERE ( ARCHIVE_TRANSACTIONS_1.TXNOPAID < 1 )
AND ( ARCHIVE_TRANSACTIONS_1.ARCHIVE_FLAG <= 1 )
AND (( ARCHIVE_TRANSACTIONS_1.TX_BALANCE >= 0.01 )
OR ( ARCHIVE_TRANSACTIONS_1.COPAYBALANCE >= 0.01 )
) AND ( ARCHIVE_TRANSACTIONS_1.PATNO > 0 )
-
6. Re: Working Firebird query produces error in Tableau Custom SQL
Tom WApr 20, 2017 3:38 PM (in response to mark.welsh)
1 of 1 people found this helpfulIf the SQL works fine outside of Tableau then it's got something to do with the driver or how Tableau is preparing the SQL statement. Nothing obvious jumps out as me as being incorrect. Normally in these situations I'd recommend you try to break it down into smaller pieces to try and isolate the error. I.e. just select from ARCHIVE_TRANSACTIONS first without the joins and the where. If that works, introduce your first join, second and so on.
Have you created a case with Tableau Support? They are in the best position to troubleshoot this further and replicate.
-
7. Re: Working Firebird query produces error in Tableau Custom SQL
Dmitry ChirkovApr 20, 2017 10:34 PM (in response to mark.welsh)
1 of 1 people found this helpfulCan you try naming all the columns?
Tableau wraps your statement in SELECT * FROM (<your SQL>) so perhaps something is going wrong with that. You can find full query in the logs (log.txt).
As Tom says - start small and see at what step does it break.
-
8. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Apr 21, 2017 8:18 AM (in response to Dmitry Chirkov) -
9. Re: Working Firebird query produces error in Tableau Custom SQL
Tom WApr 21, 2017 11:08 AM (in response to mark.welsh)
1 of 1 people found this helpfulProbably overflowing the max length allowed. You could try use a function like LEFT, MID or SUBSTRING (whichever is available in your database system) to take a portion of the column.
-
10. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Apr 21, 2017 11:15 AM (in response to Tom W)Thanks Tom. Tried LEFT and although it works in FlameRobin it does not work in Tableau. I will contact support. You've been very helpful!
-
11. Re: Working Firebird query produces error in Tableau Custom SQL
mark.welsh Apr 27, 2017 11:50 AM (in response to mark.welsh)Tableau support helped me identify that the problem is because the field is a BLOB type which is unsupported by Tableau Desktop. I was able to find syntax to convert the BLOB to a VARCHAR here: http://www.vdespa.de/blog/17-firebird-converting-blob-binary-column-in-string
Hope this helps someone else!