| Deleting Problem using PL/SQL [message #329422] |
Wed, 25 June 2008 06:59  |
basildon
Messages: 2
Registered: June 2008
|
Junior Member |
|
|
Ran the following succesfully except when I added the piece of script at the bottom to DELETE, I was given error message saying D.Mission_date needed to be declared.
I'm new to this could someone please enlighten?
Thanks
DECLARE
CURSOR incsec IS
SELECT d.mission_id id,
mt.description descript, d.mission_date
FROM DAY2_EX1 d
INNER JOIN mission_types mt
ON d.mission_type_id = mt.mission_type_id;
BEGIN
FOR m_scan IN incsec LOOP
IF m_scan.descript Like 'Assa%' THEN
UPDATE DAY2_EX1 d
SET d.security_level = d.security_level +1
WHERE d.mission_id = m_scan.id;
ELSIF d.mission_date > '31/Dec/2001' THEN
DELETE FROM DAY2_EX1 d
WHERE d.mission_id = m_scan.id;
END IF; [/B]
END LOOP;
END;
/
[Make post less unreadable]
[Updated on: Wed, 25 June 2008 07:13] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Deleting Problem using PL/SQL [message #329426 is a reply to message #329422] |
Wed, 25 June 2008 07:15  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Compare your two IF statements:
IF m_scan.descript Like 'Assa%' THEN
and
ELSIF d.mission_date > '31/Dec/2001' THEN
Now, given that your loop statement is like this:FOR m_scan IN incsec LOOP |
|
|
|
| Deleting Problem using PL/SQL [message #329450 is a reply to message #329422] |
Wed, 25 June 2008 08:28 |
basildon
Messages: 2
Registered: June 2008
|
Junior Member |
|
|
Ran the following succesfully except when I added the piece of script at the bottom to DELETE, I was given error message saying D.Mission_date needed to be declared.
I'm new to this could someone please enlighten?
Thanks
DECLARE
CURSOR incsec
IS
SELECT
d.mission_id id,
mt.description descript, d.mission_date
FROM
DAY2_EX1 d
INNER JOIN mission_types mt ON
d.mission_type_id = mt.mission_type_id
;
BEGIN
FOR m_scan IN incsec
LOOP
IF m_scan.descript Like 'Assa%' THEN
UPDATE DAY2_EX1 d
SET
d.security_level = d.security_level +1
WHERE
d.mission_id = m_scan.id;
ELSIF d.mission_date > '31/Dec/2001'
THEN DELETE FROM DAY2_EX1 d[/B]
WHERE
d.mission_id = m_scan.id;
END IF; [/B]
END LOOP;
END;
/
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
