I am a new Data Stage user, and below is a message I get when I am running data from OCI thru Transformer...
As I imported the table definition from Oracle, I left the columns as they were defined when imported as Timestamp. But that does not seem to work...I am having problems understanding what I am supposed to define in OCI, columns for DATE fields...Am I supposed to change it
from the way it imports it?
Can someone give me a guideline to what to have defined for these date type columns...or a better understanding of how to use DATES in Data Stage OCI...thru ETL.
The TOAD column definitions for these fields say they are DATE . Maybe I need a better understanding of Date handling with Oracle and SQL?
while processing column "MOD_TSP"
Attempt to convert String value "17-APR-01" to Timestamp type unsuccessful
Thank You,
Patricia Desiano
Anheuser-Busch, Inc.
MSG - BudNET
Data Stage - OCI and DATE usage and conversion
Patricia,
Im guessing that you are using a user defined query rather than a generated query.
My first suggestion is to try and use a generated query if you can. There is less maintenance involved and less little problems like this one. If, however, you really cant do it with a generated query, read on...
Ill do the right thing and refer you first to the documentation. I suggest you read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle. It tells you everything except explicitly what to do in a user-defined query.
[Ascential: ... how about adding this to the manual - it gets asked so many times ]
[Moderator: ... maybe we could have a standard document in this list until such time as Ascential get it included in the manual?]
What the manual tells you is that DataStage does a to_char when reading dates and a to_date when writing dates.
In your case, any date columns in your user defined query will require this form of expression:
to_char(DateColumn, YYYY-MM-DD HH25:MI:SS)
-----Original Message-----
From: Desiano, Patricia [mailto:Patricia.Desiano@anheuser-busch.com]
Sent: Thursday, 19 April 2001 8:21 AM
To: informix-datastage@oliver.com
Subject: Data Stage - OCI and DATE usage and conversion
I am a new Data Stage user, and below is a message I get when I am running data from OCI thru Transformer...
As I imported the table definition from Oracle, I left the columns as they were defined when imported as Timestamp. But that does not seem to work...I am having problems understanding what I am supposed to define in OCI, columns for DATE fields...Am I supposed to change it from the way it imports it?
Can someone give me a guideline to what to have defined for these date type columns...or a better understanding of how to use DATES in Data Stage OCI...thru ETL.
The TOAD column definitions for these fields say they are DATE . Maybe I need a better understanding of Date handling with Oracle and SQL?
while processing column "MOD_TSP"
Attempt to convert String value "17-APR-01" to Timestamp type unsuccessful
Thank You,
Patricia Desiano
Anheuser-Busch, Inc.
MSG - BudNET
Im guessing that you are using a user defined query rather than a generated query.
My first suggestion is to try and use a generated query if you can. There is less maintenance involved and less little problems like this one. If, however, you really cant do it with a generated query, read on...
Ill do the right thing and refer you first to the documentation. I suggest you read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle. It tells you everything except explicitly what to do in a user-defined query.
[Ascential: ... how about adding this to the manual - it gets asked so many times ]
[Moderator: ... maybe we could have a standard document in this list until such time as Ascential get it included in the manual?]
What the manual tells you is that DataStage does a to_char when reading dates and a to_date when writing dates.
In your case, any date columns in your user defined query will require this form of expression:
to_char(DateColumn, YYYY-MM-DD HH25:MI:SS)
-----Original Message-----
From: Desiano, Patricia [mailto:Patricia.Desiano@anheuser-busch.com]
Sent: Thursday, 19 April 2001 8:21 AM
To: informix-datastage@oliver.com
Subject: Data Stage - OCI and DATE usage and conversion
I am a new Data Stage user, and below is a message I get when I am running data from OCI thru Transformer...
As I imported the table definition from Oracle, I left the columns as they were defined when imported as Timestamp. But that does not seem to work...I am having problems understanding what I am supposed to define in OCI, columns for DATE fields...Am I supposed to change it from the way it imports it?
Can someone give me a guideline to what to have defined for these date type columns...or a better understanding of how to use DATES in Data Stage OCI...thru ETL.
The TOAD column definitions for these fields say they are DATE . Maybe I need a better understanding of Date handling with Oracle and SQL?
while processing column "MOD_TSP"
Attempt to convert String value "17-APR-01" to Timestamp type unsuccessful
Thank You,
Patricia Desiano
Anheuser-Busch, Inc.
MSG - BudNET
Hi David,
Weve in fact added a new exercise as an addendum to our training manual (North American version) using OCI stage last week. The new release will have OCI exercise as an addendum..
Regards,
Pavan Marpaka
Datawarehouse Trainer
Education Services
Ascential Software
pavan.marpaka@AscentialSoftware.com
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, April 18, 2001 7:54 PM
To: informix-datastage@oliver.com
Subject: RE: Data Stage - OCI and DATE usage and conversion
Patricia,
Im guessing that you are using a user defined query rather than a generated query.
My first suggestion is to try and use a generated query if you can. There is less maintenance involved and less little problems like this one. If, however, you really cant do it with a generated query, read on...
Ill do the right thing and refer you first to the documentation. I suggest you read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle. It tells you everything except explicitly what to do in a user-defined query.
[Ascential: ... how about adding this to the manual - it gets asked so many times ]
[Moderator: ... maybe we could have a standard document in this list until such time as Ascential get it included in the manual?]
What the manual tells you is that DataStage does a to_char when reading dates and a to_date when writing dates.
In your case, any date columns in your user defined query will require this form of expression:
to_char(DateColumn, YYYY-MM-DD HH25:MI:SS)
-----Original Message-----
From: Desiano, Patricia [mailto:Patricia.Desiano@anheuser-busch.com]
Sent: Thursday, 19 April 2001 8:21 AM
To: informix-datastage@oliver.com
Subject: Data Stage - OCI and DATE usage and conversion
I am a new Data Stage user, and below is a message I get when I am running data from OCI thru Transformer...
As I imported the table definition from Oracle, I left the columns as they were defined when imported as Timestamp. But that does not seem to work...I am having problems understanding what I am supposed to define in OCI, columns for DATE fields...Am I supposed to change it from the way it imports it?
Can someone give me a guideline to what to have defined for these date type columns...or a better understanding of how to use DATES in Data Stage OCI...thru ETL.
The TOAD column definitions for these fields say they are DATE . Maybe I need a better understanding of Date handling with Oracle and SQL?
while processing column "MOD_TSP"
Attempt to convert String value "17-APR-01" to Timestamp type unsuccessful
Thank You,
Patricia Desiano
Anheuser-Busch, Inc.
MSG - BudNET
Weve in fact added a new exercise as an addendum to our training manual (North American version) using OCI stage last week. The new release will have OCI exercise as an addendum..
Regards,
Pavan Marpaka
Datawarehouse Trainer
Education Services
Ascential Software
pavan.marpaka@AscentialSoftware.com
-----Original Message-----
From: David Barham [mailto:david@barham.hm]
Sent: Wednesday, April 18, 2001 7:54 PM
To: informix-datastage@oliver.com
Subject: RE: Data Stage - OCI and DATE usage and conversion
Patricia,
Im guessing that you are using a user defined query rather than a generated query.
My first suggestion is to try and use a generated query if you can. There is less maintenance involved and less little problems like this one. If, however, you really cant do it with a generated query, read on...
Ill do the right thing and refer you first to the documentation. I suggest you read pages 19 and 21 of the DataStage Oracle 8 Call Interface Plug-In technical bulletin which you should find on your DataStage CD in the "Packages" folder. This explains how DataStage handles dates in Oracle. It tells you everything except explicitly what to do in a user-defined query.
[Ascential: ... how about adding this to the manual - it gets asked so many times ]
[Moderator: ... maybe we could have a standard document in this list until such time as Ascential get it included in the manual?]
What the manual tells you is that DataStage does a to_char when reading dates and a to_date when writing dates.
In your case, any date columns in your user defined query will require this form of expression:
to_char(DateColumn, YYYY-MM-DD HH25:MI:SS)
-----Original Message-----
From: Desiano, Patricia [mailto:Patricia.Desiano@anheuser-busch.com]
Sent: Thursday, 19 April 2001 8:21 AM
To: informix-datastage@oliver.com
Subject: Data Stage - OCI and DATE usage and conversion
I am a new Data Stage user, and below is a message I get when I am running data from OCI thru Transformer...
As I imported the table definition from Oracle, I left the columns as they were defined when imported as Timestamp. But that does not seem to work...I am having problems understanding what I am supposed to define in OCI, columns for DATE fields...Am I supposed to change it from the way it imports it?
Can someone give me a guideline to what to have defined for these date type columns...or a better understanding of how to use DATES in Data Stage OCI...thru ETL.
The TOAD column definitions for these fields say they are DATE . Maybe I need a better understanding of Date handling with Oracle and SQL?
while processing column "MOD_TSP"
Attempt to convert String value "17-APR-01" to Timestamp type unsuccessful
Thank You,
Patricia Desiano
Anheuser-Busch, Inc.
MSG - BudNET