Issue with data type

[_AnonymousUser]

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!

[Anonymous]

Hello
please try:

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

Best regards
shong

[_AnonymousUser]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

shong

[Anonymous]

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

[Anonymous]

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