Getting error "ora-01722 invalid number" on my toracleinput component in where condition for date

[GGa1607660255]

Hello,

I am getting "ora-01722 invalid number" error in my toracleinput component in where condition for date.

i am attaching screen shot for my component below.

i am giving query like this in my toracleinput component.

"select * from table_name

where trunc(date_modified)>to_char('"+(Date)globalMap.get("g_file_load_date")+"' , 'dd-MM-yyyy') 

and trunc(date_modified)<to_char(current_date,'dd-MM-yyyy')

and gbank_id="+context.db_bankid+""

Below lines getting error :

.....

where trunc(date_modified)>to_char('"+(Date)globalMap.get("g_file_load_date")+"' , 'dd-MM-yyyy').

On my first component , i.e logs.log_table(tpostgressqlinput component) it will return date format like "dd-MM-yyyy' which i passing to my global variable component.

[Anonymous]

Hi

You are using tFlowToIterate to iterate the input rows, the expression of accessing the input column is:

row3.columName

or

(java.util.Date)globalMap.get("row3.columName")

So, try to update your query on tOracleInput and test again.

Regards

Shong

[GGa1607660255]

in my tFlowToIterate component is used "g_file_load_date" as key for "data_processed_date" as value.

so is write "to_char('"+(Date)globalMap.get("g_file_load_date")+"' , 'dd-MM-yyyy')"

in my where condition.

And my first component query is :

"Select max(data_processed_date) data_processed_date

from table where table_name=...." from which i got 'data_processed_date'i attached the screen shot for tFlowToIterate component.

[iamabhishek]

I don't think this is a Talend issue per se and could very well be with your Oracle query, the table and the datatype defined for the columns and also the data that you are trying to query.

For debugging purpose, were you able to run the query successfully in your oracle database, it's always better to print your complete query and see if it's correctly framed - think it of like a Java string.

Also, what value does context.db_bankid hold?

[GGa1607660255]

context.db_bankid not have issue in my job because it return correct value.

it getting from my master job.

i think "to_char('"+(Date)globalMap.get("g_file_load_date")+"' , 'dd-MM-yyyy')" is any syntax wrong or what?

[iamabhishek]

As I said, print the complete query and test it out in your Oracle database.

Your error says about invalid number so I doubt it has to anything with date - please paste the complete query

[GGa1607660255]

how to print complete query in talend?

[Anonymous]

Hi

Have a try to remove the single quote from the to_char() function,

to_char("+(Date)globalMap.get("g_file_load_date")+" , 'dd-MM-yyyy') 

Regards

Shong

[GGa1607660255]

Hello,

if i remove single quote from query. then it will give me error like "ORA-00907: missing right parenthesis"..

my query in toracleinput component is like :

"select * from table_name

where gbank_id="+globalMap.get("db_bankid")+" and

trunc(date_modified)>to_char("+(Date)globalMap.get("g_file_load_date")+",'dd-MM-yyyy')

"