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
Got it.
I think you misunderstood the usage of "Truncate Table".
Because TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00 and TIME_ID:20120404, CALENDAR_DAY:04-APR-2012 00:00:00 are not the same record.
You'd better choose "Drop and create table" instead of "Truncate Table".
Here is the usage of Truncate Taleb.
The data in target table.
TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00
The date in source table.
TIME_ID:20120403, CALENDAR_DAY:04-APR-2012 00:00:00
You will see these two records have the same TIME_ID and different CALENDAR_DAY.
Then your previous job will work.
In short, the records in source and target which you want to truncate should own the same key.
Regards,
Pedro

[Anonymous]

Hi
Got it.
I think you misunderstood the usage of "Truncate Table".
Because TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00 and TIME_ID:20120404, CALENDAR_DAY:04-APR-2012 00:00:00 are not the same record.
You'd better choose "Drop and create table" instead of "Truncate Table".
Here is the usage of Truncate Taleb.
The data in target table.
TIME_ID:20120403, CALENDAR_DAY:03-APR-2012 00:00:00
The date in source table.
TIME_ID:20120403, CALENDAR_DAY:04-APR-2012 00:00:00
You will see these two records have the same TIME_ID and different CALENDAR_DAY.
Then your previous job will work.
In short, the records in source and target which you want to truncate should own the same key.
Regards,
Pedro

Hi Pedro,
This information is useful and makes sense to me, i forgot that the source table is having the primary key on TIME_ID column. I have one more quick question if i use drop and create option for target table will the old structure of the target table be created or the structure of source table will be created?

[Anonymous]

Hi
The table will be created based on the schema of tOracleOutput.
Always it is the same with the target table.
One more thing. Don't forget to type "Length" of each column in tOracleOutput(Image 1).
Or you will get error.
Regards,
Pedro

[Anonymous]

Hi Pedro,
Thanks, Thank you very much this information is really useful, sure i will keep in mind to print the length of each column.
Have a wonderful day ahead!
Thanks and Regards,
Pavan

Hi
The table will be created based on the schema of tOracleOutput.
Always it is the same with the target table.
One more thing. Don't forget to type "Length" of each column in tOracleOutput.
Regards,
Pedro

[Anonymous]

Hi Pavan
Glad to help you.
Regards,
Pedro

[Anonymous]

Hi Pedro,
Can you help me how to run winzip.exe using talend?. I tried to use tFileUnarchive but it doesn't work since my file format is .tar.gz. Do you have any sample job to run winzip.exe using talend.
Thanks and Regards,
Pavan

Hi Pavan
Glad to help you.
Regards,
Pedro