Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a scenario , update query with 'WHERE' clause, inside where clause also need to get value using SELECT query. I doing this using tDBRow, but still stuck at how to get value from SELECT statement with tDBRow.
SELECT statement is not working inside where clause
Here is example:
"UPDATE table_A
SET DT1= ? ,
DT2=?,
column2= ?,
column3= ?
WHERE column4= ? AND
(column2 is null OR DT2=? ) AND
DT3 = (SELECT MAX(DT3) FROM table_A WHERE column1=? ) "
Are there always multiple rows read from the source file? You need to iterate each row so as to you are able to access the current value of columns.
only 1 row in each file.
If there is only one row in each file, you can build the query string on tJavaRow and execute the query on tDBRow, eg:
tFileList--iterate--tFileInputDelimited--tMap--out-->tJavaRow--oncomponentok--tDBRow
on tJavaRow:
context.query="update tableName set fieldName1="+input_row.column1+" and fieldName2="+input_row.column2
//context.query is a context variable, string type.
//to access the value of column from input row using expression: input_row.columnName
on tDBrow:
set the Query field with context variable as:
context.query
Hope it help you!
Regards
Shong
Hi Shong,
Thanks for your answer. I tried it with simple update query with hardcoding values and with input row values. still no luck. Getting error " row1 cannot be resolved to a variable" at tJavaRow component. Please help me
Context Variable declarationFlow
context.query in tJavaRow
Query in tDBRow
can you share the code of tJavaRow? I see some code reference to row1
only this update qury i am using tJavaRow
only this update query , using in tJavaRow
Hi Sheng,
Plesase help me out this.