topic Re: Query tdbinput in Talend Studio

Query tdbinput

CL_1608507129 — Sun, 20 Dec 2020 23:48:12 GMT

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!

Re: Query tdbinput

Anonymous — Mon, 21 Dec 2020 03:29:21 GMT

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

Re: Query tdbinput

CL_1608507129 — Mon, 21 Dec 2020 09:01:07 GMT

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

Re: Query tdbinput

Anonymous — Tue, 22 Dec 2020 09:04:56 GMT

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

Re: Query tdbinput

CL_1608507129 — Tue, 22 Dec 2020 21:39:33 GMT

My data type of LOAD_DATE field in Oracle is DATE