Query tdbinput

[CL_1608507129]

Hi, I am trying to run the following query through the tdbInput component.

I try to get the result including in the where "between or <= and> =" but it doesn't work, it is not the same result as my query in oracle. It only returns correct values ​​when the value is "="

"SELECT

 NUMBER,

STATUS,

 LOAD_DATE

FROM COM

where (to_char(LOAD_DATE,'dd-mm-yyyy') BETWEEN '" + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", ((String)globalMap.get("01032019")) ))+ "' AND '"

 + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", ((String)globalMap.get("05032019")) ))+"')

"

-------------------------------------------------------------------------------------------------------------------

"SELECT

 NUMBER,

STATUS,

 LOAD_DATE

FROM COM

where (

 to_char(LOAD_DATE,'dd-mm-yyyy') >= '" + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", "01032019" ))+ "' AND to_char(LOAD_DATE,'dd-mm-yyyy') <='"

 + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", "05032019" ))+"'

 )

"

Thanks a lot!

[Anonymous]

Hi

I think using the syntax <= and> = should work, for debugging, have you tried to execute the query with fixed value, eg:

"SELECT

 NUMBER,

STATUS,

 LOAD_DATE

FROM COM

where 

 load_date>= '18-12-2020' and load_date <= '21-12-2020'"

If this query works, there must be something wrong in the transformation when using oracle function or talend function.

Regards

Shong

"

[CL_1608507129]

Hi,

thanks for the reply.

I have tried to perform your query

"SELECT

 NUMBER,

STATUS,

 LOAD_DATE

FROM COM

where 

 load_date>= '18-12-2020' and load_date <= '21-12-2020'"

And I have the following error:

Exception in component tDBInput_1 (REQ)

java.sql.SQLException: ORA-01861: literal does not match format string.

I'm trying to pass two String type global variables to the query and it dosn´t work

varFechaIni = "01032019"

varFechaFin = "05032019"

"SELECT

 NUMBER,

STATUS,

 LOAD_DATE

FROM COM

where (to_char(LOAD_DATE,'dd-mm-yyyy') BETWEEN '" + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", ((String)globalMap.get("varFechaIni")) ))+ "' AND '"

 + TalendDate.formatDate("dd-MM-yyyy",TalendDate.parseDate("ddMMyyyy", ((String)globalMap.get("varFechaFin")) ))+"')

"

Thanks

[Anonymous]

What is the data type of LOAD_DATE field in Oracle table？

[CL_1608507129]

My data type of LOAD_DATE field in Oracle is DATE