topic Issue with data type in Talend Studio

Issue with data type

_AnonymousUser — Sat, 16 Nov 2024 13:50:50 GMT

Hi There,
I am using Datastage from last few years. I am analysing Talend and till now finding it quite good. I am stuck in defining a data type -
I am using following query in Oracle tOracleInput :
NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),'12') CAMPAIGN_MNTH
CAMPAIGN_MNTH is defined as integer but it gives data type error -
Exception in component tOracleInput_1
java.sql.SQLException: Fail to convert to internal representation
----
To counter issue I modified query to
to_char(NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),'12')) CAMPAIGN_MNTH now getting this error:-
ORA-01722: invalid number
---
I tried all data types but no luck.. So its not handling in short. Please advice what needs to be done.
Thanks!

Re: Issue with data type

Anonymous — Tue, 04 Aug 2009 09:47:30 GMT

Hello
please try:

"select NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),12)  from yourTableName"

Best regards
shong

Re: Issue with data type

_AnonymousUser — Wed, 05 Aug 2009 01:54:30 GMT

Hi Shong,
Thanks for you reply.
It works very well when I use only that statement. Actually this statement is a part of my bit big query:
"select to_char(CP_ID) CAMPAIGN_CODE, trim(CP_NAME) CAMPAIGN_NAME,
NVL(CP_DESC, '-=-') CAMPAIGN_DESC,
CP_DESC CAMPAIGN_DESC_Out,
NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),12) CAMPAIGN_MNTH,
SOURCE_RECORD_ID
from (
SELECT CP_ID,CP_NAME,CP_DESC,CAMPAIGN_START_DATE,SOURCE_RECORD_ID
FROM CP_SRC WHERE (upper(CP_name) like '%SQ%' or upper(CP_NAME) like '%SM_SQ%' or upper(CP_desc) like '%CM%') AND upper(CP_NAME) NOT LIKE '%C3S%'
union
SELECT TAG_ID,TAG_NAME,TAG_DESCRIPTION,TAG_START_DATE,SOURCE_RECORD_ID
FROM MIT_arc_TAG_src WHERE (upper(CP_name) like '%SQ%' or upper(CP_desc) like '%SMM_1WQ%' or upper(CP_NAME) like '%CM%') AND upper(CP_NAME) NOT LIKE '%C3S%') "
When I remove NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),12) CAMPAIGN_MNTH from query it works flawlessly but once its in it gives error:
Exception in component tOracleInput_1
java.sql.SQLException: Fail to convert to internal representation
Initially I thought that statement does not work but on your suggestion when I tried it in individual job without quote it worked well. Its just not working in query. This statement is copied from Datastage job which works fine.
Thanks mate

Re: Issue with data type

Anonymous — Wed, 05 Aug 2009 03:20:31 GMT

Hello
What's the data type of schema for CAMPAIGN_MNTH column? Can you check your schema mapping?
Best regards
shong

Re: Issue with data type

Anonymous — Wed, 05 Aug 2009 05:18:14 GMT

Hi Shong,
Campaign_Mnth is populated by NVL(EXTRACT(MONTH FROM CAMPAIGN_START_DATE),12) CAMPAIGN_MNTH. Campaign_Start_Date is Date datatype and I have defined Campaign_Mnth as integer.
Thats the complete error log:
connected
Exception in component tOracleInput_1
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.CharCommonAccessor.getInt(CharCommonAccessor.java:141)
at oracle.jdbc.driver.T4CVarcharAccessor.getInt(T4CVarcharAccessor.java:779)
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:712)
at test.cp_stg_frmwrk_0_1.CP_STG_FRMWRK.tOracleInput_1Process(CP_STG_FRMWRK.java:876)
at test.cp_stg_frmwrk_0_1.CP_STG_FRMWRK.tOracleConnection_1Process(CP_STG_FRMWRK.java:259)
at test.cp_stg_frmwrk_0_1.CP_STG_FRMWRK.runJobInTOS(CP_STG_FRMWRK.java:1232)
at test.cp_stg_frmwrk_0_1.CP_STG_FRMWRK.main(CP_STG_FRMWRK.java:1141)
disconnected
Job CP_STG_FRMWRK ended at 14:13 05/08/2009.

Appreciate your support mate,
Regards

Re: Issue with data type

Anonymous — Wed, 05 Aug 2009 08:13:04 GMT

Hello
Please export your job(right click on the job name and select 'export item' option in Repository) and send it to me. I will debug the job.
Best regards

shong

Re: Issue with data type

Anonymous — Thu, 06 Aug 2009 00:28:37 GMT

Hi Shong,
Thanks for your help mate.
I sent mail to you with job as attachment. I have removed some info as I am testing Talend as an option for future ETL in my company by testing performance and ease by developing Datastage jobs in Talend.
Regards

Re: Issue with data type

Anonymous — Thu, 06 Aug 2009 03:28:39 GMT

Hello guy
I have imported your job and found that you don't have correct schema mapping, see my screenshot.
Best regards

shong

Re: Issue with data type

Anonymous — Thu, 06 Aug 2009 05:14:59 GMT

Thanks a lot Mate,
I never thought that Talend needs column names in sync to the way they used in query. I thought it will catch destination names according to the column names used in query.
I am analysing this tool to propose for future works. I may be asking for help in future.
Best Regards
Amit

Re: Issue with data type

Anonymous — Thu, 14 Mar 2019 05:33:12 GMT

Hi Shong,

I am running a job in Talendand it gives me this error

"Exception in component tDBInput_1 (MATT_NEW)
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.CharCommonAccessor.getBigDecimal(CharCommonAccessor.java:414)
at oracle.jdbc.driver.OracleResultSetImpl.getBigDecimal(OracleResultSetImpl.java:548)
at local_project.matt_new_0_1.MATT_NEW.tDBInput_1Process(MATT_NEW.java:3628)
at local_project.matt_new_0_1.MATT_NEW.tDBConnection_1Process(MATT_NEW.java:707)
at local_project.matt_new_0_1.MATT_NEW.runJobInTOS(MATT_NEW.java:10822)
at local_project.matt_new_0_1.MATT_NEW.main(MATT_NEW.java:10584)"

I have check every field and still dont understand why am i getting this error. Can you please help me with this asap?

Thanks,

Alekhya

Re: Issue with data type

Anonymous — Fri, 15 Mar 2019 14:50:33 GMT

@Alekhya05

It has something to do with your data type in input DB (which seems to be in Big Decimal format) and the output String format.

Before going to other details, I would request you to put your query to a new post. The current post is 10 years old and is a closed post. So if you put a totally different subject in same post, it will dilute the purpose of creating multiple posts for each query.

Hope you understands.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Issue with data type

Anonymous — Fri, 15 Mar 2019 17:02:15 GMT

Sure, thanks Nikhil. I have that query resolved. But have another question which I think I have posted already about Data Truncation error in Talend. I will post that as a new post.

Thanks,

Alekhya

Re: Issue with data type

Anonymous — Tue, 18 Jun 2019 09:04:41 GMT

Hi shong,

can you please help me with this.

Exception in component tOracleInput_1
java.sql.SQLException: Fail to convert to internal representation
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:403)
at oracle.jdbc.driver.CharCommonAccessor.getBigDecimal(CharCommonAccessor.java:357)
at oracle.jdbc.driver.OracleResultSetImpl.getBigDecimal(OracleResultSetImpl.java:426)
at local_project.v8_cust_0_1.v8_cust.tOracleInput_1Process(v8_cust.java:3468)
at local_project.v8_cust_0_1.v8_cust.runJobInTOS(v8_cust.java:5097)
at local_project.v8_cust_0_1.v8_cust.main(v8_cust.java:4954)
[statistics] disconnected
Job v8_cust ended at 09:44 18/06/2019. [exit code=1]