Query Regarding Column Generated query in OCI stage

[ashik_punar]

Hi Everyone,

I am using an OCI stage to read some data from Oracle 10g table. In the SQL i am selecting the 'Column Generated SQL query' option and trying to select a date field from the source table.Everything is working fine but the problem is that in the generated SQL it is using TO_CHAR function. I am not able to understand why this is happening. The target field in which i am fetching the date field is also of TimeStamp datatype.
For example:

I have a column Business_Date in the source table.The column generated SQL query is something like this :

SELECT TO_CHAR(ODSADM.TW_IMX_BAL.BUSINESS_DATE, 'YYYY-MM-DD HH24:MI:SS')

The target column in which i am selecting the source field is also of TimeStamp datatype.
Now my question is why its getting converted to CHAR.

Thanks a lot for all the support and help that everyone has been provided.

Thanks in advance,

[ray.wurlod]

Because DataStage server jobs do not have an intrinsic Timestamp data type.

[kumar_s]

Date in Oracle has timestamp portion in it. Where as Datastage dosen't have one. Hence the Data data type is mapped to timestamp data type in datastage, with to_char() during select and to_date() to perform the vice versa during insert.

[ashik_punar]

Hi Ray/Kumar,

Thanks a lot for the quick reply. I got the answer.

Now i have one more doubt. In the OCI stage what is the purpose of the Derivation column.I went to the columns tab and loaded the columns of a table, after that i decided to load columns of some other table which is having the same structure. When i am doing the same all the columns are getting replaced but their derivation in the derivation is same.
For example:
I have 2 tables:
US_BALANCE
UK_BALANCE

both these tables are in schema1 and their column structure also is same.Say column1 and column2.

Now i loaded the table structure for table US_BALANCE and the derivations column is having 'schema1.US_BALANCE.column1' for the first column and 'schema1.US_BALANCE.column2' for the sceond column.After some time i decided to load the table structure for UK_BALANCE in the same OCI Stage.When i am doing the same it is asking me if i want to replace column1 and column2.If i say yes it replaces the same but the derivation column is not updated.ideally it should have 'schema1.UK_BALANCE.column1' but it is still having 'schema1.US_BALANCE.column1' . Can you please guide me on the reason for the same.

Thanks a lot for all the help that you have been providing.

Thanks in Advacne,

[kumar_s]

user 'Synchronise Columns' Option available in Query tab.

[ashik_punar]

Hi Kumar,

Thanks a lots for the guidance.