Insert a varibale coming from an excel in a query

Help Studio
1

Hello!
Trying to enter a variable that comes from an excel in a query I get the following error( the variable was transformed from DataTable to String )

“Run query: ERROR [42601] [IBM][CLI Driver][DB2] SQL0104N An unexpected “117197979” symbol was encountered following “”. Expected symbols may include: “AND * AT OR SELECTIVITY YEAR YEARS MONTH MONTHS DAY DAYS DAYS HO”. SQLSTATE=42601”

This is the query
“INSERT INTO CAG.ANT8TB01 SELECT * FROM CAG.AN84TB01 WHERE AN840040 = “+nucs2+” order by AN840060 asc”

what could be happening? thanks!

2

Hi @Juan_Antonio_Goncalves_Ro

Try the below query:

"INSERT INTO CAG.ANT8TB01 SELECT * FROM CAG.AN84TB01 WHERE AN840040 = '" + nucs2 + "' ORDER BY AN840060 ASC"

Hope it helps!!

3

I have also tried this way and I get this failure:

Run query: ERROR [22018] [IBM][CLI Driver][DB2] SQL0420N An invalid character was found in a character string argument of the “DECFLOAT” function. SQLSTATE=22018

I don’t know why it doesn’t let me, I have also tried to use the DataTable type variable and put the .ToString and nothing.
Thanks!

4

Hi @Juan_Antonio_Goncalves_Ro

Since nucs2 is a datatable before excecuting the SQL query, use Output Data Table activity and pass the data table variable. The output of the activity will be a string.

After that try passing that variable in the sql query:
eg after using Output Data Table activity the output of that would be output_nucs2. Use output_nucs2 in the query

"INSERT INTO CAG.ANT8TB01 SELECT * FROM CAG.AN84TB01 WHERE AN840040 = '" + output_nucs2+ "' ORDER BY AN840060 ASC"

Hope it helps!!

6

Yes, that’s exactly what I’m doing, but I still don’t have a solution. :frowning: I get the same message

7

if possible can you share the screenshot of the workflow @Juan_Antonio_Goncalves_Ro

Regards

8

image
image1521×731 181 KB

“CurrentRow2” is the value I pass from DataTable to String

9

Hi @Juan_Antonio_Goncalves_Ro

Can I know what is exact process you are doing?

Regards

10

Read a column of an excel with some IDs, each id is stored in a variable and this variable must be searched in the query that I am trying to do, this with a for each so that it goes through each cell of the column.

Thanks!

11

You can try this way:
=> Use Read Range Workbook to read the excel and store it in an datatable
=> Use For Each row in Data Table to iterate through the datatable and you can assign the ID’s by below way:

str_ID= CurrentRow("YourID column name").ToString

After that you can use Connect To DataBase and execute Query and pass the query:

"INSERT INTO CAG.ANT8TB01 SELECT * FROM CAG.AN84TB01 WHERE AN840040 = '" + str_ID+ "' ORDER BY AN840060 ASC"

In this case there is no need of Output Data Table and everything.

Hope it helps!!
Regards

12

Hi

Seems like you are passing the entire datarow where you have to be passing an individual value.
Try accessing the column value thats is required and pass it as a string

Ex: currentrow2(“column-name”).toString

13

Hello!
I am not passing a DataRow because I am checking the data I am using by writing a line with the data that is already passed to String and that variable is the one I am using in the query like this.

image
image1689×804 65.8 KB

Regards!