I am testing a loop dataset automation task where I pull Id numbers that are strings from a .csv file and search for them in a database. In the database they are also defined as strings. Because they are numeric I'm getting an error:
File_1 - Implicit conversion from datatype 'VARCHAR' to 'NUMERIC' is not allowed. Use the CONVERT function to run this query.
This seems to similar to this issue:
https://forums.toadworld.com/t/toad-datapoint-4-0-string-variable-problem-with-numerics/39342
I've tried CONVERT(VARCHAR, :myVariable) but then the query hangs. myVariable is already a Varchar so this should not be necessary.
Any help is appreciated.
Welcome to the TDP Forum...
Might have to have you share your automation logic or extra details, but here are a couple "work-arounds" you may want to try...
Is it possible to strip off the quotes from your "stringed" IDs in the flat file? Either with a file system utility before reading it into your automation job, or by executing a Transform & Cleanse task (to convert the datatype) before processing the file.
If the datatypes can't be converted with a function call, or a utility, or Transform & Cleanse (unlikely, but possible for whatever reason), then can you create a temp table on a (relational) database? There, you can use a database function to perform more heavy-duty datatype conversions.
1 Like
Thanks very much Gary. The script needs to see the input as a string but it thinks it's a number, even though in the file it is a string with quotes around it. The database being searched is also a string. No matter what I do - such as adding quotes around the input parameter - nothing works.
I don't have the ability to create a temporary table in this database either.