Convert Rows to a List [message #8715] |
Fri, 19 September 2003 17:16 |
John R
Messages: 156 Registered: March 2000
|
Senior Member |
|
|
I have a parent table called Change_Management. It has two children tables called CM_Affected_Device and CM_Change_Schedule each with multiple rows for each row in the Change_Management table. I want to return a result set with a list of Affected Devices and scheduled start times as well as a CR Number and Descrition. I'm attempting to create a cursor on the Change_Management table and loop through each child table creating a list of matching elements.
Here what I have so far. Not sure what do to next.
DECLARE
CURSOR thisCursor IS
select
c.Change_Management_ID, m.status
from
CM_P1.cm_change_management c,
CM_P1.cm_change_management_status_m m
where
c.change_management_id IN
(
select change_management_id from CM_P1.CM_Change_Schedule_M s
where s.change_start_date >= #createODBCDateTime(sDateDO)# AND s.change_start_date < #createODBCDateTime(eDateDO)#
)
AND c.change_management_id = m.change_management_id
CMIDVal cm_change_management%TYPE;
StatusVal cm_change_management_status_m%TYPE;
BEGIN
OPEN thisCursor;
LOOP
Fetch thisCursor INTO CMIDVal, StatusVal
/* affected device */
select device_name from CM_P1.cm_affected_device_m d where d.change_management_id = CMIDVal
/* scheduled start */
select change_start_date from CM_P1.cm_change_schedule_m s where s.change_management_id = CMIDVal
EXIT WHEN thisCursor%NOTFOUND
END LOOP;
CLOSE CURSOR;
END
|
|
|