Home » SQL & PL/SQL » SQL & PL/SQL » Convert Rows to a List
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
Previous Topic: Average date/time
Next Topic: how can I find total number of lines of source code
Goto Forum:
  


Current Time: Thu Mar 28 14:49:10 CDT 2024