One Star

Issue with data type

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!

8 REPLIES
Community Manager

Re:Issue with data type

Hello
please try:

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


Best regards
shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re:Issue with data type

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

Community Manager

Re:Issue with data type

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re:Issue with data type

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

Community Manager

Re:Issue with data type

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

----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Issue with data type

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

Community Manager

Re:Issue with data type

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

shong

----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re:Issue with data type

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