How to Replace Default Date Value if Date Field is NULL with PL/SQL

The NVL is a very good SQL function that works with an Oracle Database. NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVL returns expr1.

Here is a sample:

select    id,
nvl(to_char(order_date, 'MM/DD/YYYY'), '*No-Date*') "Order Date",
nvl(to_char(ship_date, 'MM/DD/YYYY'), '*No-Date*')    "Shipped Date",
NVL(TO_CHAR(ROUND(MONTHS_BETWEEN(ship_date, order_date))), '*NA*') "#-Months"
from orders
/

Results * * * *

ID Order Date Shipped Da #-Months
---------- ---------- ---------- ----------------------------------------
10 *No-Date*  *No-Date*  *NA*
11 07/27/1998 08/31/1999 13
12 12/04/1996 01/08/1998 13
13 01/07/1999 02/11/2000 13
14 07/30/1998 09/03/1999 13
15 *No-Date*  *No-Date*  *NA*
16 04/11/1998 05/16/1999 13
17 12/29/1996 02/02/1998 13
18 05/25/1998 06/29/1999 13
19 08/09/1998 09/13/1999 13
20 *No-Date*  *No-Date*  *NA*
21 06/22/1998 07/27/1999 13
22 06/23/1999 07/27/2000 13


About onlinejt

Blogger

,

No comments yet.

Leave a Reply

*