tOracleOutput doesn't "Truncate Insert" values into target table

[Anonymous]

Hi All,
I have a problem which is not making me sleep, kindly if any one knows please help me out. I will explain you my situation below
Database racle 11g
Oracle SQl developer:Version 3.1.06
OS:Windows Server 2008 R2 Datacenter
TOS for DI:5.0.1.r74687
Scenario:
It's asimple job but making me not to sleep from last two days, here are the details of the situation
1. I have two Oracle tables one Source and another destination table. I want to move the data from source to target.
2. I have used tOracleInput(Source Table name IM_TIME) and tOracleOutput(Target Table Name:TEMP_DAILY_FINANCE_RPT_CAL).
3. Source table contains many columns but i want only two columns data (time_id (Number), calander_date(Date)).
4. Target table contains only two columns with same column definition as of source table (time_id (Number), calander_date(Date)).
5. In Source table i am using a condition in select statement to retieve data which is part of our business logic, the logic is working correctly and even i am
able to print the output of "Select" statement in tJava component (Testing Purpose).
6. So every thing is fine till here now, so i have given connection to my Target table from Source and i have put "Truncate Insert" in Target table
7. Saved my job and run my job(F6), job gets executed successfully with our errors, which means
as per the job it should Truncate the Target table and insert the output of Select statement in to Target table, but it doesn't happen at all


tOracleInput -------> tOracleOutput
I tried to attach the Screen Shots of my job so that it will make you better understand my situation but i think there is problem with uploading images. My all images were below 200KB. I could only attach only one image i had really good images for the above situation explained but this is very embrassing that i could not upload images, since i am not able to upload images i will explain in detail the SQL statement used in Source table (tOracleInput) below,
"SELECT time_id, calendar_date
FROM edw.dim_time
WHERE
calendar_date ='"+TalendDate.formatDate("dd-MMM-yy", TalendDate.addDate(TalendDate.parseDate("dd-MMM-yy HH:mm:ss",
TalendDate.getDate("dd-MMM-yy HH:mm:ss")), -2, "dd"))+"'"

The above mentioned SQL statement is the one which i use in Source Table(tOracleInput).

Kindly help me with a solution, please correct me if i am wrong any where. Waiting for reply.
Thanks and Regards,
Pavan

[Anonymous]

Hi Pavan
You'd better check this job step by step.
No.1: Link tOracleInput with tLogRow. Can you see the output rows occur?
If the result is 0 row, it means the query is wrong.
No.2: Code in tJava as follow.

String date=TalendDate.formatDate("dd-MMM-yy", TalendDate.addDate(TalendDate.parseDate("dd-MMM-yy HH:mm:ss",
TalendDate.getDate("dd-MMM-yy HH:mm:ss")), -2, "dd"));
System.out.println(date);

Is this time correct?
Because the DB is Oracle, you'd better use to_date(), SYSDATE() methods which are predefined in Oracle instead of TalendDate.

trunc(sysdate)-2 
or
to_char(sysdate-2,'dd-MMM-yy')

Regards,
Pedro

[Anonymous]

Hi Pedro,
Thanks for the quick reply, i tried both things mentioned by you and both are working perfectly and i am getting output when i use tLogrow.
I am attaching the image of tLogrow output which i got. But i think you understood my problem the selected values are not getting inserted in target table, first of all the target table itself is not getting truncated
Thanks and Regards,
Pavan

[Anonymous]

Hi Pavan
Now I think this is your requirement.

1. I have two Oracle tables one Source and another destination table. I want to move the data from source to target.
2. I have used tOracleInput(Source Table name:DIM_TIME) and tOracleOutput(Target Table Name:TEMP_DAILY_FINANCE_RPT_CAL).
3. Source table contains many columns but i want only two columns data (time_id (Number), calander_date(Date)).
4. Target table contains only two columns with same column definition as of source table (time_id (Number), calander_date(Date)).

Why don't you create a job as follow?
tOracleInput--main-->tFilterColumn--main-->tOracleOutput
The function of tFilterColumn is to get time_id (Number), calander_date(Date) only.
Why do you use "Truncate Table" here?
If you want to do ETL from one Oracle Table to another one, you'd better choose "Create table if not exist".
Regards,
Pedro

[Anonymous]

Hi Pedro,
you mean to say that instead of using the entire Talend expression, use simply trunc(sysdate)-2 or to_char(sysdate-2,'dd-MMM-yy') pre-defined oracle functions in the select query which i had written in tOracleInput, am i correct?
And coming to your question why Truncate Insert instead of "Create table if not exists" option, the explanation is as follows
The target table definition is already defined and created. I use Truncate Insert because the value which gets inserted in the target table will be used by another tables for updating values.
And thank you for your suggestion to use tFilter, I would rather use tMap where i have control over mapping.
But if this " trunc(sysdate)-2 or to_char(sysdate-2,'dd-MMM-yy')" will work out in place of the Talend stmt which i used in select query that will make my job more easier
What do you suggest?
Thanks and Regards,
Pavan

[Anonymous]

Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

[Anonymous]

Hi Pedro,
OK, let me put in single statement, "The job executes perfectly but my purpose of doing that job is not served"
After the job gets executed the target table should be truncated and insert the values in the target table based on the select query output
I attach image of the output that the target table currently has, the output shows it has one value in the table, so what i want now is if i run my talend job it should truncate the old value and insert new value i.,e TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00, but this is not happening
Forgive me if my English confuses you, i am sorry for that.
Thanks and Regards,
Pavan

Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

[Anonymous]

Hi Pedro,
I tried to attach the image but the system is not accepting. I think now you should be clear with the problem that i am facing which i mentioned below
Thanks and Regards,
Pavan

Hi Pedro,
OK, let me put in single statement, "The job executes perfectly but my purpose of doing that job is not served"
After the job gets executed the target table should be truncated and insert the values in the target table based on the select query output
I attach image of the output that the target table currently has, the output shows it has one value in the table, so what i want now is if i run my talend job it should truncate the old value and insert new value i.,e TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00, but this is not happening
Forgive me if my English confuses you, i am sorry for that.
Thanks and Regards,
Pavan
Hi Pavan
Now forget all the methods about to_date() or TalendDate.
Please show me the issue you got. A screenshot is preferred.
Or I may misunderstand your requirement.
Regards,
Pedro

[Anonymous]

Hi Pavan
The size of this uploaded image should be less then 1024x768 pixels.
How many rows in target table before you run the job?
Regards,
Pedro

[Anonymous]

Hi Pedro,
The target table at any time contains only one row. Before i run my job also only one row is present, but the values should change after running the job. Here you go with the image attached

Hi Pavan
The size of this uploaded image should be less then 1024x768 pixels.
How many rows in target table before you run the job?
Regards,
Pedro