Home » SQL & PL/SQL » SQL & PL/SQL » Oracle Float to Date Conversion (Oracle 11g,Win XP)
Oracle Float to Date Conversion [message #396523] |
Tue, 07 April 2009 02:09  |
|
Hi,
I have a problem, we use OWB for ETL. We have 2 schemas(Source and Target).
CREATE TABLE source1(date1 FLOAT);
CREATE TABLE target1(date1 DATE,date2 DATE);
INSERT INTO source1
(date1
)
VALUES (20080101
);
INSERT INTO source1
(date1
)
VALUES (20070101
);
INSERT INTO source1
(date1
)
VALUES (20090101
);
I have written the following query,
SELECT TO_DATE (TO_NUMBER (date1), 'yyyymmdd')
FROM source1
INSERT INTO target1
(date1, date2)
SELECT TO_DATE (TO_NUMBER (date1), 'yyyymmdd'), trunc(SYSDATE)
FROM source1
I have the following concerns,
1. Is it the only way to convert the Float to Date ?
2. what if my Float column has 0 Value, I am currently planning to put '01-01-1900' as the default value into Target Table. (Will use Case When Statement to check for null )
3. If you people came across this situation, what do you put in the Date1 column of TARGET1 table ? Will you put '01-01-1900' as default ? I cannot put '0' directly because its a DATE column.
4. Once Date1,Date2 column are loaded i need to Substract these two columns.
Regards,
Ashoka BL
|
|
|
|
Re: Oracle Float to Date Conversion [message #396534 is a reply to message #396523] |
Tue, 07 April 2009 02:34   |
|
@Michel,
Thanks for the quick reply.
Quote: |
2. what if my Float column has 0 Value, I am currently planning to put '01-01-1900' as the default value into Target Table. (Will use Case When Statement to check for null )
|
I have a twist here, I cannot load Null because its a Key column, i just created the tables (source1,target1) only for simplicity here, the actual table is having a NOT NULL Constraint, so i cannot put Null there.
Generally if you come across this situation, what shall you put ? .
Once i put the data, then i have to Subtract Date1 with Date2 as well.
Regards,
Ashoka BL
|
|
|
|
Re: Oracle Float to Date Conversion [message #396549 is a reply to message #396523] |
Tue, 07 April 2009 03:26   |
|
Thanks Michel.
Actually our design is like that, the ETL Specs what we recieved is having like that( The column is the part of the Primary key).
CREATE TABLE source1(date1 FLOAT);
CREATE TABLE target1(date1 DATE not null,date2 date);
INSERT INTO source1
(date1
)
VALUES (20080101
);
INSERT INTO source1
(date1
)
VALUES (20070101
);
INSERT INTO source1
(date1
)
VALUES (null
);
If you have a above situation, how do u load Target Table ??
I wrote the following query,but obviously will give an error.
INSERT INTO target1
(date1, date2)
SELECT NVL (TO_DATE (TO_NUMBER (date1), 'yyyymmdd'), 0), TRUNC (SYSDATE)
FROM source1
Regards,
Ashoka BL
|
|
|
|
|
Re: Oracle Float to Date Conversion [message #396615 is a reply to message #396534] |
Tue, 07 April 2009 06:01   |
joicejohn
Messages: 327 Registered: March 2008 Location: India
|
Senior Member |
|
|
@ashoka_bl,
ashoka_bl wrote on Tue, 07 April 2009 13:04 |
I cannot load Null because its a Key column, i just created the tables (source1,target1) only for simplicity here, the actual table is having a NOT NULL Constraint, so i cannot put Null there.
Generally if you come across this situation, what shall you put ? .
Once i put the data, then i have to Subtract Date1 with Date2 as well.
|
Well, a lot depends on the business requirements on how to implement the default's for unknown/NULL values. You have to understand that each column of data in the target table of a data warehouse/data mart is reflecting some information for the key columns.
My advice will be start analyzing the Source Data, like
1. Why data in source column comes as NULL?
2. What will be the impact of the target table data depending on this NULL value?
3. What will be the impact of the calculation if a NULL value comes up. For instance, for Date1-Date2 if you add a Default value to one of the columns, how is it going to affect your ETL process as a whole?
The best person to raise these doubts will be your data modeler who might have analyzed these data. Keep Michel's suggestion in mind.
Quote: |
If some data are not known then it should be NULL and this columns can't be (part of) a primary key (it could be a unique one).
Any arbitrary value will fool the optimizer or collide with actual value.
|
Just added my thoughts...
May be a more experienced person can share his/her opinion...
Regards,
Jo
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 15:43:36 CST 2025
|