Re: SQL Question: Date Substitution on NULL fields

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/04
Message-ID: <33e6298f.21502428@newshost>#1/1

use NVL or decode.

For example:

create view 'table'
select nvl( date_field, '00/00/00' ) from T

or

create view 'table'
select decode( date_field, NULL, '00/00/00', to_char( date_field, 'DD/MM/YY' ) )

from T

Now, select * from 'table' will give you what you want...

On Mon, 4 Aug 1997 12:29:07 -0400, "David Sivick" <dsivick_at_mindspring.com> wrote:

> How can I get the null fields in a column type DATE to be represented by
>some other field in a select statement. I don't want to update the fields
>in the table, I just want the field to show up as something else in the
>select.
>
>For Example:
>
>Purchase_No Item Date
>------------------------------------------------
>0145 Boots
>0146 Socks 01/01/97
>0147 Tie 11/11/96
>
>
>So my select would be:
>
>select purchase_no,date from 'table'
>
>And I want to get back...
>
>0145 00/00/00
>0146 01/01/97
>0147 11/11/96
>
>I have tried to fool the replace function, but since I am not dealing with a
>character field, it is not being freindly.
>
>Thanks,
>David
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Aug 04 1997 - 00:00:00 CDT