Transcript SQL functions

SQL functions - numeric and date
Speaker notes contain additional
information!
Numeric functions
PAY_
---1111
2222
3333
4444
5555
6666
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
JO
-CI
IN
AP
CM
CI
IN
STARTDATE
SALARY
BONUS
--------- --------- --------15-JAN-97
45000
1000
25-SEP-92
40000
1500
05-FEB-00
25000
500
03-JUL-97
42000
2000
30-OCT-92
50000
2000
18-AUG-94
48000
2000
SQL> SELECT ROUND(salary/12,2)
2 FROM first_pay;
ROUND(SALARY/12,2)
-----------------3750
3333.33
2083.33
3500
4166.67
4000
number of
decimal places
This command is rounding
the calculation of salary
divided by 12. It is rounding
to 2 decimal places.
Numeric functions
SQL> SELECT salary/7
2 FROM first_pay;
SALARY/7
--------6428.5714
5714.2857
3571.4286
6000
7142.8571
6857.1429
Both rounding and truncating are
Rounding goes to .29, truncating
Rounding goes to .43, truncating
No rounding or truncating
Rounding goes to .86, truncating
Both rounding and truncating are
.57
leaves at .28
leaves at .42
leaves at .85
.14
SQL> SELECT ROUND(salary/7,2), TRUNC(salary/7,2)
2 FROM first_pay;
ROUND(SALARY/7,2) TRUNC(SALARY/7,2)
----------------- ----------------6428.57
6428.57
5714.29
5714.28
3571.43
3571.42
6000
6000
7142.86
7142.85
6857.14
6857.14
Numeric functions
SQL> SELECT salary, MOD(salary,3)
2 FROM first_pay;
SALARY MOD(SALARY,3)
--------- ------------45000
0
40000
1
25000
1
42000
0
50000
2
48000
0
SQL> SELECT salary, MOD(salary,7)
2 FROM first_pay;
SALARY MOD(SALARY,7)
--------- ------------45000
4
40000
2
25000
3
42000
0
50000
6
48000
1
SQL> SELECT salary, MOD(salary, 12)
2 FROM first_pay;
SALARY MOD(SALARY,12)
--------- -------------45000
0
40000
4
25000
4
42000
0
50000
8
48000
0
number dividing by
MOD returns the remainder that
results from the divide. There are
three different examples. Note that
the format is MOD(column,number
you are dividing by)
Numeric functions
SQL> SELECT salary/7, CEIL(salary/7), FLOOR(salary/7)
2 FROM first_pay;
SALARY/7 CEIL(SALARY/7) FLOOR(SALARY/7)
--------- -------------- --------------6428.5714
6429
6428
5714.2857
5715
5714
3571.4286
3572
3571
6000
6000
6000
7142.8571
7143
7142
6857.1429
6858
6857
SQL> SELECT salary/7, CEIL(salary/7)
2 FROM first_pay
3 WHERE CEIL(salary/7) = ROUND(salary/7,0);
SALARY/7 CEIL(SALARY/7)
--------- -------------6428.5714
6429
6000
6000
7142.8571
7143
This shows CEIL
and FLOOR in
calculations and
CEIL in a WHERE
clause comparison.
Numeric functions
Format: POWER(column or formula, power)
To the second power
To the third power
To the forth power
SQL> SELECT bonus, POWER(bonus,2), POWER(bonus/100,3), POWER(bonus/100,4)
2 FROM first_pay;
BONUS POWER(BONUS,2) POWER(BONUS/100,3) POWER(BONUS/100,4)
--------- -------------- ------------------ -----------------1000
1000000
1000
10000
1500
2250000
3375
50625
500
250000
125
625
2000
4000000
8000
160000
2000
4000000
8000
160000
2000
4000000
8000
160000
Numeric functions
SIGN(MOD(bonus,bonus) means bonus is divided
by bonus and the sign of the remainder is checked
to see if it is 0, >0, <0
SQL> SELECT bonus, SIGN(MOD(bonus,bonus)), SIGN(bonus/-1), SIGN(bonus)
2 FROM first_pay;
BONUS SIGN(MOD(BONUS,BONUS)) SIGN(BONUS/-1) SIGN(BONUS)
--------- ---------------------- -------------- ----------1000
0
-1
1
1500
0
-1
1
500
0
-1
1
2000
0
-1
1
2000
0
-1
1
2000
0
-1
1
SIGN returns a 0 if the number being
evaluated is a 0, a 1 if the number being
evaluated is >0, and a -1 if the number
being evaluated is <0.
Date function
Sysdate gets the date from the system. Notice
the default presentation.
SQL> SELECT sysdate
2 FROM dual;
SYSDATE
--------02-JUN-00
SQL> SELECT sysdate
2 FROM sys.dual;
SYSDATE
--------02-JUN-00
Date functions
SQL> SELECT sysdate, ROUND(sysdate), TRUNC(sysdate)
2 FROM dual;
SYSDATE
ROUND(SYS TRUNC(SYS
--------- --------- --------03-JUN-00 03-JUN-00 03-JUN-00
I did the first date at 7:10 in the AM.
NOTE: All three dates are the same.
I then went into the system clock and
changed it to PM. When I ran the
command again, ROUND went to the
next day.
SQL> SELECT sysdate, ROUND(sysdate), TRUNC(sysdate)
2 FROM dual;
SYSDATE
ROUND(SYS TRUNC(SYS
--------- --------- --------03-JUN-00 04-JUN-00 03-JUN-00
Date functions
SQL> SELECT startdate, ROUND(startdate), TRUNC(startdate)
2 FROM first_pay;
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
ROUND(STA
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
TRUNC(STA
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
The default on ROUND and
TRUNC is day. MONTH and
YEAR can be specified.
SQL> SELECT ROUND(startdate,'MONTH'),TRUNC(startdate,'MONTH')
2 FROM first_pay;
ROUND(STA
--------01-JAN-97
01-OCT-92
01-FEB-00
01-JUL-97
01-NOV-92
01-SEP-94
TRUNC(STA
--------01-JAN-97
01-SEP-92
01-FEB-00
01-JUL-97
01-OCT-92
01-AUG-94
When ROUND is used with MONTH, dates
with days such as 25-SEP are rounded to OCT
and 30-OCT are rounded to NOV. Dates with
15-JAN and 05-FEB are simply rounded to the
first day of the month.
NOTE: The day is 01 when rounded or
truncated.
Date functions
SQL> SELECT startdate, ROUND(startdate,'YEAR'), TRUNC(startdate,'YEAR')
2 FROM first_pay;
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
ROUND(STA
--------01-JAN-97
01-JAN-93
01-JAN-00
01-JAN-98
01-JAN-93
01-JAN-95
TRUNC(STA
--------01-JAN-97
01-JAN-92
01-JAN-00
01-JAN-97
01-JAN-92
01-JAN-94
When the ROUND and TRUNC are
done to a year 01-JAN are used for
day and month. For months like
JUL,AUG, SEP and OCT ROUND
shows the next year.
For dates like JAN and FEB
ROUND shows the same year.
Date functions
SQL> SELECT name, startdate, MONTHS_BETWEEN(startdate, sysdate)
2 FROM first_pay;
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
STARTDATE MONTHS_BETWEEN(STARTDATE,SYSDATE)
--------- --------------------------------15-JAN-97
1159.3767
25-SEP-92
1107.6992
05-FEB-00
-3.945912
03-JUL-97
1165
30-OCT-92
1108.8605
18-AUG-94
1130.4734
THE SYSDATE being used is 03-JUN-00. Note that 05-FEB-00
is -3.9… months back. Looking at 15-JAN-97 you can see that
the number is going forward. The assumption is being made that
the first two digits are 20, and we are looking forward and taking
the difference between 15-JAN-2000 and 03-JUN-2000. This is
because this table was created in 2000 and therefore the date was
stored with the 20.
Date functions
SQL> SELECT datefst, MONTHS_BETWEEN(sysdate, datefst) FROM donor;
DATEFST
MONTHS_BETWEEN(SYSDATE,DATEFST)
--------- ------------------------------03-JUL-98
23
24-MAY-97
36.353125
03-JAN-98
29
04-MAR-92
98.998286
04-MAR-92
98.998286
04-APR-98
25.998286
Since this table was created in 1999,
the data is calculated with dates in
19__. NOTE: the whole year is
actually stored with the date.
There are 23 months
between 03-JUL-98 and
03-JUN-00 (the sysdate).
Date functions
SQL> SELECT startdate, TO_CHAR(startdate,'MM/DD/YYYY') FROM first_pay;
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
TO_CHAR(STARTDATE,'MM/DD/YYYY')
------------------------------------------------------------01/15/2097
TO_CHAR will be covered later in this presentation.
09/25/2092
It converts dates or numbers to character fields using
02/05/2000
a specified format. In this case, the format is
07/03/2097
MM/DD/YYYY to show the four character year.
10/30/2092
08/18/2094
When I show the date in a format that shows the four digit year, I can see that the first_pay database
which was created in 2000 has all the years in 2000. The donor database which was created in 1999
has all the years in the 1990s.
SQL> SELECT datefst, TO_CHAR(datefst, 'MM/DD/YYYY') FROM donor;
DATEFST
--------03-JUL-98
24-MAY-97
03-JAN-98
04-MAR-92
04-MAR-92
04-APR-98
TO_CHAR(DATEFST,'MM/DD/YYYY')
------------------------------------------------------07/03/1998
05/24/1997
01/03/1998
03/04/1992
03/04/1992
04/04/1998
Date function
SQL> SELECT name, startdate, ADD_MONTHS(startdate,6), ADD_MONTHS(startdate,-6)
2 FROM first_pay;
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
ADD_MONTH
--------15-JUL-97
25-MAR-93
05-AUG-00
03-JAN-98
30-APR-93
18-FEB-95
ADD_MONTH
--------15-JUL-96
25-MAR-92
05-AUG-99
03-JAN-97
30-APR-92
18-FEB-94
In one example went ahead
and then back 6 months, in
the other example I used 2
months. Notice that the
difference between 2000 and
1999 are handled correctly.
SQL> SELECT name, startdate, ADD_MONTHS(startdate,2), ADD_MONTHS(startdate,-2)
2 FROM first_pay;
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
ADD_MONTH
--------15-MAR-97
25-NOV-92
05-APR-00
03-SEP-97
30-DEC-92
18-OCT-94
ADD_MONTH
--------15-NOV-96
25-JUL-92
05-DEC-99
03-MAY-97
30-AUG-92
18-JUN-94
Date function
SQL> SELECT startdate, NEXT_DAY(startdate,'MONDAY')
2 FROM first_pay;
STARTDATE
--------15-JAN-97
25-SEP-92
05-FEB-00
03-JUL-97
30-OCT-92
18-AUG-94
NEXT_DAY(
--------21-JAN-97
29-SEP-92
07-FEB-00
08-JUL-97
03-NOV-92
23-AUG-94
Looking at the dates in first_pay, I am
determining the next time a MONDAY will
occur starting from that date.
For example, 05-FEB-00 is a SATURDAY, so
the next time a MONDAY will occur is 07FEB-00.
SQL> SELECT sysdate, NEXT_DAY(sysdate,'TUESDAY')
2 FROM dual;
SYSDATE
NEXT_DAY(
--------- --------03-JUN-00 06-JUN-00
Today is a SATURDAY, so in fact the
next time a TUESDAY occurs will be
3 days from now on 06-JUN-00.
Conversion functions
SQL> SELECT name, startdate, TO_CHAR(startdate,'MM/YYYY')
2 FROM first_pay;
NAME
STARTDATE
-------------------- --------TO_CHAR(STARTDATE,'MM/YYYY')
-------------------------------------------------------------------------Linda Costa
15-JAN-97
The TO_CHAR startdate has a lot of
01/2097
padding and so it wraps - even on the
Oracle screen. The default column size is
John Davidson
25-SEP-92
80 characters. This can be resized with the
09/2092
column command to be covered later.
Susan Ash
02/2000
05-FEB-00
Stephen York
07/2097
03-JUL-97
Richard Jones
10/2092
30-OCT-92
Joanne Brown
08/2094
18-AUG-94
Note: The month is displayed in number
format because of the MM which returns 112 and the year is displayed as a 4 digit year
because of the YYYY format.
See notes for additional information.
Conversion functions
SQL> SELECT name, startdate, TO_CHAR(startdate,'fmMM/YYYY')
2 FROM first_pay;
NAME
STARTDATE
-------------------- --------TO_CHAR(STARTDATE,'FMMM/YYYY')
------------------------------------------------------------Linda Costa
15-JAN-97
1/2097
The fm element can be used to leading
John Davidson
25-SEP-92
zeros or padded blanks. In this case it
9/2092
will remove the leading 0 from the month.
Susan Ash
2/2000
05-FEB-00
Stephen York
7/2097
03-JUL-97
Richard Jones
10/2092
30-OCT-92
Joanne Brown
8/2094
18-AUG-94
Conversion functions
SQL> SELECT sysdate, TO_CHAR(sysdate,'Q')
2 FROM dual;
Q returns the quarter.
June is in the second
quarter.
SYSDATE
TO_CHAR(SYSDATE,'Q')
--------- --------------------------------------------------------04-JUN-00 2
SQL> SELECT sysdate, TO_CHAR(sysdate,'fmDDMONTHYYYY')
2 FROM dual;
NOTE the difference
between the format
SYSDATE
TO_CHAR(SYSDATE,'FMDDMONTHYYYY')
without spaces between
--------- -------------------------------elements and with spaces
04-JUN-00 4JUNE2000
between elements.
SQL> SELECT sysdate, TO_CHAR(sysdate, 'fmDD MONTH YYYY')
2 FROM dual;
SYSDATE
TO_CHAR(SYSDATE,'FMDDMONTHYYYY')
--------- -------------------------------04-JUN-00 4 JUNE 2000
fmDD returns the day with leading zeros removed
MONTH returns the month in words
YYYY returns the year in 4 digits
Conversion functions
SQL> SELECT TO_CHAR(sysdate,'fmDD "of" MONTH')
2 FROM dual;
Double quotes are used to
enclose character strings that
are embedded in the format.
TO_CHAR(SYSDATE,'FMDD"OF"MONTH')
---------------------------------------------4 of JUNE
DDSP means
spelled out DD.
SQL> SELECT TO_CHAR(sysdate, '"DAY" DDSP "OF" MONTH')
2 FROM dual;
TO_CHAR(SYSDATE,'"DAY"DDSP"OF"MONTH')
------------------------------------------------------DAY FOUR OF JUNE
SQL> SELECT TO_CHAR(startdate, 'fmDDTH "of" MONTH')
2 FROM first_pay;
TO_CHAR(STARTDATE,'FMDDTH"OF"MONTH')
-----------------------------------------------------15TH of JANUARY
25TH of SEPTEMBER
5TH of FEBRUARY
3RD of JULY
30TH of OCTOBER
18TH of AUGUST
DDTH adds
TH or other
appropriate
clause to date.
Conversion functions
SQL> SELECT TO_CHAR(sysdate,'HH24:MI:SS:AM')
2 FROM dual;
TO_CHAR(SYSDATE,'HH24:MI:SS:AM')
-------------------------------------------14:23:48:PM
SQL> SELECT TO_CHAR(sysdate, 'HH12:MI:SS:AM')
2 FROM dual;
TO_CHAR(SYSDATE,'HH12:MI:SS:AM')
---------------------------------------------02:24:56:PM
SQL> SELECT TO_CHAR(sysdate, 'HH:MI:SS')
2 FROM dual;
TO_CHAR(SYSDATE,'HH:MI:SS')
----------------------------------------02:31:17
Note that AM
changes to PM when
appropriate. PM
could also have been
used.
HH24 is 24 hour clock and
HH12 is 12 hour clock. HH
hour of day.
Conversion functions
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RR';
Session altered.
SQL> INSERT INTO first_pay
2 VALUES('8888','Paula Adams','IN','12-DEC-98',45000,2000);
1 row created.
SQL> SELECT name, TO_CHAR(startdate,'DD-MON-YYYY')
2 FROM first_pay;
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
Donald Brown
Paula Adams
8 rows selected.
TO_CHAR(STARTDATE,'DD-MON-YYYY')
------------------------------------------15-JAN-2097
25-SEP-2092
The session is altered as
05-FEB-2000
explained in the speaker notes.
03-JUL-2097
Therefore, Paula Adams is given
30-OCT-2092
a year of 1998 instead of 2098.
18-AUG-2094
05-NOV-2099
12-DEC-1998
Conversion functions
SQL> UPDATE first_pay
2 SET startdate = '05-NOV-99'
3 WHERE name = 'Donald Brown';
1 row updated.
I am still in the session
where RR is established so
when I change the date for
Donald Brown, it becomes
1999 instead of the previous
2099.
SQL> SELECT name, TO_CHAR(startdate,'DD-MON-YYYY')
2 FROM first_pay;
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
Donald Brown
Paula Adams
TO_CHAR(STARTDATE,'DD-MON-YYYY')
---------------------------------15-JAN-2097
25-SEP-2092
05-FEB-2000
03-JUL-2097
30-OCT-2092
18-AUG-2094
05-NOV-1999
12-DEC-1998
Conversion functions
SQL> SELECT TO_CHAR(sysdate, 'fmDDSPTH "day of" MONTH", "YYYY "at" HH12 AM')
2 FROM dual;
TO_CHAR(SYSDATE,'FMDDSPTH"DAYOF"MONTH","YYYY"AT"HH12AM')
--------------------------------------------------------------------------FOURTH day of JUNE, 2000 at 4 PM
Note: MONTH”, “YYYY means
that there is no space after month
and only the space embedded in
the literal before year. Notice
also that HH gives the hour only.
The actual time is 4:07 but I did
not ask for minutes.
Conversion functions
SQL> DESC first_pay;
Name
Null?
------------------------------- -------PAY_ID
NAME
JOBCODE
STARTDATE
SALARY
BONUS
Type
---VARCHAR2(4)
VARCHAR2(20)
CHAR(2)
DATE
NUMBER(9,2)
NUMBER(5)
Note that BONUS has no decimal places, put in formatting I want it to
display with decimal places. This can be done through formatting.
SQL> SELECT pay_id, name, TO_CHAR(salary,'$99,999.99'), TO_CHAR(bonus,'$99,999.99')
2 FROM first_pay;
PAY_
---1111
2222
3333
4444
5555
6666
7777
8888
NAME
TO_CHAR(SAL TO_CHAR(BON
-------------------- ----------- ----------Linda Costa
$45,000.00
$1,000.00
John Davidson
$40,000.00
$1,500.00
Susan Ash
$25,000.00
$500.00
Stephen York
$42,000.00
$2,000.00
Richard Jones
$50,000.00
$2,000.00
Joanne Brown
$48,000.00
$2,000.00
Donald Brown
$45,000.00
$2,000.00
Paula Adams
$45,000.00
$2,000.00
This is a floating $ sign that floats up
to the first significant digit. Note that
with 500 the comma gets suppressed
because there is no significant digit in
the thousands position.
Conversion functions
SQL> SELECT name, TO_CHAR(salary,'99,999.99'), TO_CHAR(bonus, '9,999')
2 FROM first_pay;
NAME
TO_CHAR(SA TO_CHA
-------------------- ---------- -----Linda Costa
45,000.00 1,000
John Davidson
40,000.00 1,500
Susan Ash
25,000.00
500
Stephen York
42,000.00 2,000
Richard Jones
50,000.00 2,000
Joanne Brown
48,000.00 2,000
Donald Brown
45,000.00 2,000
Paula Adams
45,000.00 2,000
Editing without the dollar sign but
with the comma inserted. Leading
zeros do not display.
SQL> SELECT name, TO_CHAR(salary, '99999.99'), TO_CHAR(bonus, '0,009')
2 FROM first_pay;
NAME
TO_CHAR(S TO_CHA
-------------------- --------- -----Linda Costa
45000.00 1,000
John Davidson
40000.00 1,500
Susan Ash
25000.00 0,500
Stephen York
42000.00 2,000
Richard Jones
50000.00 2,000
Joanne Brown
48000.00 2,000
Donald Brown
45000.00 2,000
Paula Adams
45000.00 2,000
There is no comma specified
for salary and bonus has
leading zeros being displayed.
Conversion functions
SQL> SELECT TO_CHAR(-567,'999MI'), TO_CHAR(-0123, '9,999PR')
2 FROM dual;
TO_C TO_CHAR
---- ------567<123>
MI prints the minus sign at the end and PR
encloses the negative data in <…>. Note that if
the data is not negative there is no special
indication.
SQL> SELECT TO_CHAR(1243, '9999MI'), TO_CHAR(345,'999PR')
2 FROM dual;
TO_CH TO_CH
----- ----1243
345
Conversion functions
SQL> DESC first_pay;
Name
Null?
------------------------------- -------PAY_ID
NAME
JOBCODE
STARTDATE
SALARY
BONUS
SQL> SELECT pay_id, TO_NUMBER(pay_id)
2 FROM first_pay;
PAY_ TO_NUMBER(PAY_ID)
---- ----------------1111
1111
2222
2222
3333
3333
4444
4444
5555
5555
6666
6666
7777
7777
8888
8888
In the first example, I converted
pay_id to a number - you can see it
aligned to the right of the field.
In the second example, I compared
the converted pay_id to a number.
SQL> SELECT *
2 FROM first_pay
3 WHERE TO_NUMBER(pay_id) = 2222;
PAY_ NAME
JO STARTDATE
SALARY
BONUS
---- -------------------- -- --------- --------- --------2222 John Davidson
IN 25-SEP-92
40000
1500
Type
---VARCHAR2(4)
VARCHAR2(20)
CHAR(2)
DATE
NUMBER(9,2)
NUMBER(5)
Conversion functions
SQL> SELECT * FROM first_pay;
PAY_
---1111
2222
3333
4444
5555
6666
7777
8888
NAME
-------------------Linda Costa
John Davidson
Susan Ash
Stephen York
Richard Jones
Joanne Brown
Donald Brown
Paula Adams
8 rows selected.
JO
-CI
IN
AP
CM
CI
IN
CI
IN
STARTDATE
SALARY
BONUS
--------- --------- --------15-JAN-97
45000
1000
25-SEP-92
40000
1500
05-FEB-00
25000
500
03-JUL-97
42000
2000
30-OCT-92
50000
2000
18-AUG-94
48000
2000
05-NOV-99
45000
2000
12-DEC-98
45000
2000
Since the system date is in 2000, the assumption is that I
am looking for dates greater than June 15, 2097. Only
one meets this criteria. The last two dates have 19 as
their first two digits.
SQL> SELECT *
2 FROM first_pay
3 WHERE startdate > TO_DATE('15-JUN-97');
PAY_ NAME
JO STARTDATE
SALARY
BONUS
---- -------------------- -- --------- --------- --------4444 Stephen York
CM 03-JUL-97
42000
2000
Conversion functions
SQL> SELECT name, yrgoal, NVL(yrgoal,0)
2 FROM donor;
NAME
YRGOAL NVL(YRGOAL,0)
--------------- --------- ------------Stephen Daniels
500
500
Jennifer Ames
400
400
Carl Hersey
0
Susan Ash
100
100
Nancy Taylor
50
50
Robert Brooks
50
50
SQL> SELECT name, yrgoal, NVL(yrgoal, -1000)
2 FROM donor;
NAME
YRGOAL NVL(YRGOAL,-1000)
--------------- --------- ----------------Stephen Daniels
500
500
Jennifer Ames
400
400
Carl Hersey
-1000
Susan Ash
100
100
Nancy Taylor
50
50
Robert Brooks
50
50
In one example, I
displayed null and in
the other example I
displayed -1000 in the
column where the null
value occurred.