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 Go to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #396528 is a reply to message #396523] Tue, 07 April 2009 02:18 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1. Yes except as source1.date1 is a number you must not use TO_NUMBER.

2. Use DECODE.

3. NULL.

4. Do it.

Regards
Michel
Re: Oracle Float to Date Conversion [message #396534 is a reply to message #396523] Tue, 07 April 2009 02:34 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

@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 #396536 is a reply to message #396534] Tue, 07 April 2009 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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.

Quote:
Once i put the data, then i have to Subtract Date1 with Date2 as well.

Then do it, what is the problem?

Regards
Michel
Re: Oracle Float to Date Conversion [message #396549 is a reply to message #396523] Tue, 07 April 2009 03:26 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

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 #396554 is a reply to message #396549] Tue, 07 April 2009 03:49 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
To avoid error I will code like this

INSERT INTO target1
            (date1, date2)
   SELECT TO_DATE (TO_NUMBER (date1), 'yyyymmdd'), TRUNC (SYSDATE)
     FROM source1 
    WHERE date1 is not null; 

[Updated on: Tue, 07 April 2009 03:51]

Report message to a moderator

Re: Oracle Float to Date Conversion [message #396604 is a reply to message #396549] Tue, 07 April 2009 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
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

First correct what I said in my first reply.
Then use something that is meaningful.

Regards
Michel
Re: Oracle Float to Date Conversion [message #396615 is a reply to message #396534] Tue, 07 April 2009 06:01 Go to previous messageGo to next message
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
Re: Oracle Float to Date Conversion [message #396623 is a reply to message #396523] Tue, 07 April 2009 06:13 Go to previous message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

Jo,

Thanks for your valuable suggestions, i will dfntly look into the points that you mentioned.

I wanted a general opinion on this, If a kind of situation come then how do we act.

Regards,
Ashoka BL
Previous Topic: How to call a procedure in INSERT Trigger
Next Topic: How to cut a string in one string. [merged]
Goto Forum:
  


Current Time: Sat Feb 08 15:43:36 CST 2025