nulls into Oracle Oracle

[Johnson, Harold A EDUC:EX]

thanks.  this is only a one shot deal - but does anyone know why the 1858
date placed into the OO database instead of an actual null?  Just old PH
behaviour?



-----Original Message-----
From: Darren Reely [mailto:darren.reely@latticesemi.com]
Sent: Wednesday, April 30, 2003 5:07 PM
Cc: 'powerh-l@lists.swau.edu'
Subject: Re: nulls into Oracle Oracle


Harold,

If your processing can't wait to change the value after inserting into 
your OO database, then you may want to try triggers against your new OO 
tables.  I only think this is valuable if your transfers are a 
reoccurring action. Not a one shot move from RDB to OO.  For a one shot 
I'd go with Blues suggestion of converting the date after inserting your 
records.

I'm no trigger expert but came up with the following...

CREATE OR REPLACE TRIGGER
my_table_bir
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
        select decode(:new.inactived_date ,'18581117' ,null 
,:new.inactived_date)
                ,decode(:new.active_date ,'18581117' ,null 
,:new.active_date)
        into :new.inactived_date
                , :new.active_date
        from dual;
        :new.inactived_date := :new.inactived_date;
        :new.active_date := :new.active_date;
END;
/

The decode says to place NULL into the :new.inactived_date column if the 
value passed in is 18581117, otherwise keep the value passed in.  Notice 
I can affect more than one column.

NOTE: I've altered my personal date format from the Oracle default.

Darren

P.S.  Use at your own risk. My test used four records.


Johnson, Harold A EDUC:EX wrote:

> Hi all.   I'm using VMS Powerhouse (830d) to convert data directly 
> from an RDB database to an Oracle Oracle database.  When I assign a 
> field to the "OO" database, a NULL value from RDB gets converted to 
> that old "1858/11/17 00:00:00.00" null date.  Is there anyway to force 
> an OO null into the field without checking every single field in RDB 
> for "if ... is null"?????
>  
> If I don't assign anything to a field, then it gets assigned properly 
> to a "~".
>  
>  
> thnx!
>  
>  



= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l@lists.swau.edu
Subscribe: "subscribe" in message body to powerh-l-request@lists.swau.edu
Unsubscribe: "unsubscribe <password>" in message body to
powerh-l-request@lists.swau.edu
http://lists.swau.edu/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.