Using Text Box value at runtime as a numeric value in SQL code

Question

I usually use a Text Box to get a value from user at runtime and simply use the %Question.AnnotationName% in my SQL inside Input Data to process. I have never had an issue with it when using with DB2, however now I'm trying to use the same with an SAP HANA source table, and I keep getting a String to number conversion error when doing my typical:

WHERE YM IN ('%Question.YM%')

I've tried  WHERE YM =  cast('%Question.YM%' as integer) but that doesn't work.

Any insight appreciated.

dralban · Answer

Thanks for responding. Yes, I have tried every combination I could think of, including no quotes. It basically looks like the error checking happens before substitution takes place, because I can actually RUN this code, and it works, but gives an error when doing Test Query...

danilang · Answer

Hi @dralban

In most SQL dialects, integers don't require quotation marks around them.  Have you tried

WHERE YM IN (%Question.YM%)

Dan

apathetichell · Answer

https://community.tableau.com/s/question/0D54T00000C5ijA/how-to-convert-a-string-to-an-integer-from-sap-hana-database

Since it seems like this is more of a SAP Hana thing than an Alteryx specific thing - have you checked out this thread on integer conversions on SAP Hana/Tableau? It seems like it could be null()s which are throwing it?