Hi,
Can anyone tell me how to read/write the columns of datatype INTERVAL in NETEZZA and ORACLE databases?
INTERVAL datatype stores the time-span.But there is not datatype called INTERVAL in datastage. I am using Datastage EE 7.5.2.
INTERVAL datatype handling in Datastage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 3
- Joined: Thu Aug 07, 2008 11:05 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3
- Joined: Thu Aug 07, 2008 11:05 pm
Re: INTERVAL datatype handling in Datastage
anil.kumarm wrote:Hi,
Can anyone tell me how to read/write the columns of datatype INTERVAL in NETEZZA and ORACLE databases?
INTERVAL datatype stores the time-span.But there is not datatype called INTERVAL in datastage. I am using Datastage EE 7.5.2.
An example of the data is as given below:
CHR_NM_ID
-------------------
3 days 04:33:01.3
3 days 12:30:06.7
3 days 12:30:06.7
3 years 4 mons
9 days 04:33:01.3
The metadata of the filed is defined in the table as follows:
Attribute | Type | Modifier | Default Value
-----------+--------------------+----------+---------------
CHR_NM_ID | INTERVAL | NOT NULL |
When i try to import the metadata from netezza database,into the datastage, the datatype is coming as VARCHAR into the datastage.
Does this mean that datastage doesnot support INTERVAL datatype?If so how can we handle this in datastage?
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 3
- Joined: Thu Aug 07, 2008 11:05 pm
Re: INTERVAL datatype handling in Datastage
Ray,
When you import teh metadata inot datastage VARCHAR datatype will come in place on INTERVAL. But when you are trying to read/insert the data by keeping the datatype as VARCHAR, the datastage throws an error "main_program: Datatype Not Supported ". So i am not able to read the data and neither i am able to write teh data to the table
When you import teh metadata inot datastage VARCHAR datatype will come in place on INTERVAL. But when you are trying to read/insert the data by keeping the datatype as VARCHAR, the datastage throws an error "main_program: Datatype Not Supported ". So i am not able to read the data and neither i am able to write teh data to the table
The INTERVAL datatype is not directly supported, but I'm not sure there's really a need to. Typically they are handled as strings, so a varchar should be perfectly satisfactory. Or a numeric datatype if all you want is an individual component of the interval.
Can't speak to DB2 but seeing as how these INTERVAL datatypes are a SQL 99 standard I imagine it's the same as the Oracle implementation. Which means you would use INTERVAL when working with the full duration and EXTRACT when you want an individual component. Since it's not supported natively there's no generated sql option, so user-defined / custom sql would be the order of the day for this.
There are thousands of helpful references out there in the tubes for this that a simple google would turn up. See if any of them tickle your fancy. One such example:
http://www.psoug.org/reference/timestamp.html
Can't speak to DB2 but seeing as how these INTERVAL datatypes are a SQL 99 standard I imagine it's the same as the Oracle implementation. Which means you would use INTERVAL when working with the full duration and EXTRACT when you want an individual component. Since it's not supported natively there's no generated sql option, so user-defined / custom sql would be the order of the day for this.
There are thousands of helpful references out there in the tubes for this that a simple google would turn up. See if any of them tickle your fancy. One such example:
http://www.psoug.org/reference/timestamp.html
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers