Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
My job design is as follow:
tprejob -> tmysqlinput (date is in date datatype) -> tjavarow tp store the date in the context param
context param is of string type
tjavarow code:
context.last_run = TalendDate.formatDate("yyyy-MM-dd", row9.txndate);
System.out.println("Last run " + context.last_run);
when I am using this to get the data in main job using below query facing issue:
SELECT
a.no,
a.col2,
b.col3
b.datecol -- date datatype
FROM table1 a
inner join table2 b
on a.no=b.no
where b.
datecol
>= ' "+ context.last_run + " 'Error:
Exception in component tDBInput_1 (Copy_of_realtime)
java.sql.SQLException: Incorrect DATE value: ' "+ context.last_run + " '
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129)
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
at com.mysql.cj.jdbc.StatementImpl.executeQuery(StatementImpl.java:1218)
Kindly help. Thanks
@Shicong Hong ,
I dont want to iterate at once. I wanted to use the same query as below
SELECT
a.no,
a.col2,
b.col3
b.datecol -- date datatype
FROM table1 a
inner join table2 b
where b.
datecol
>= ' "+ context.last_run + " '
for 1st load - 2022-07-27
It is taking the data greater or equal to 27
for 2nd load - 2022-07-28
It should take data from 2022-02-28 onwards but it is taking data from previous date as well which is wrong.
Is there any wrong in above sql as when I am running the same query in mysql getting data greater than or equal to 2022-07-28 only.
Also tmysqlinput query with hardcode value works fine.
@guenneguez jeremy
You mean like below:
where b.
cast(datecol as date)
>= ' "+ context.last_run + " '
The date format which I want is yyyy-MM-dd and it is coming from both the Tjava and Mysql components.
@Vasim Ahmed Mwith your job design, the query will run only one time, the last value '2022-07-28' will be assigned to context.last_run.
I tried to run the query alone by disabling the other subjobs but still getting all the records including past date.
Query with hardcode value is running fine in mysql.
Its still giving past data as well.
@Shicong Hong I am using query as below > instead of >= :
select col1,col2 from table1 , table2
on table1.no=table2.no
where substring(date_field,1,10)>'"+context.last_run+"'
still It is giving the past date data.
can you help here?
Hi
Should use where date_field >'"+context.last_run+"' to filter data on a Date column. For testing, also query date_field column in the select list.
@Shicong Hong
Actually while just selecting date_field column I am getting value as 2022-07-22 00:00:00.00 hence I am using substring to compare the exact value.