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,
Query Regarding Column Generated query in OCI stage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 71
- Joined: Mon Nov 13, 2006 12:40 am
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Premium Member
- Posts: 71
- Joined: Mon Nov 13, 2006 12:40 am
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,
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,
-
- Premium Member
- Posts: 71
- Joined: Mon Nov 13, 2006 12:40 am