| Help with NULL in LOOP [message #3056] |
Mon, 02 September 2002 20:29  |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
LOOK AT THIS
DECLARE
v_comm CONSTANT NUMBER := 0;
BEGIN
LOOP
UPDATE emp
SET comm = NVL(comm, v_comm)
WHERE comm IS NULL;
EXIT WHEN v_comm IS NOT NULL;
END LOOP;
END;
How is it checking is v_comm IS NOT NULL when it has a contant of 0... BUT IT WORKS.... or is it chicking if all the comm fields are not null....
|
|
|
|
| Re: Help with NULL in LOOP [message #3068 is a reply to message #3056] |
Tue, 03 September 2002 01:29  |
Keith
Messages: 88
Registered: March 2000
|
Member |
|
|
v_comm will always be not null as you've initialised it equal to 0.
Your loop doesn't really do anything though - it'll basically open, perform the update against all records with the null comm value and then exit as the exit condition is satisifed as soon as the loop is opened. If you were to take out the EXIT WHEN line, you'd get an infinite loop.
Hope this helps - if not, post what you're trying to do and I'll have a look
|
|
|
|
| Re: Help with NULL in LOOP [message #3080 is a reply to message #3056] |
Tue, 03 September 2002 09:30 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
The NVL function is checking if COMM is null, but as Keith mentions this code block (and loop) is totally unnecessary. It should be simply:
UPDATE emp
SET comm = 0
WHERE comm IS NULL;
The WHERE clause is limiting the update to rows with a NULL commission, so there is no need to even use the NVL function.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
