How to union these two procedures and get the result in oracle SQL.
PROCEDURE getSessionforRecording (
isessionids IN VARCHAR2,
ischoolname IN VARCHAR2,
iprojectname IN VARCHAR2,
istudentname IN VARCHAR2,
itutorname IN VARCHAR2,
ifromdate IN VARCHAR2,
itodate IN VARCHAR2,
ocursor OUT t_cursor
) IS
BEGIN
OPEN ocursor FOR SELECT
(ses.session_id)session_id,
(ses.board_refid)wb_session_id,
(sf.session_rating)recording_priority,
sf.tutor_rating,
ses.subject_name,
(ses.course_name) topic_name,
(ses.lesson_objective ) question,
( st.first_name|| ','|| st.last_name ) student_name,
( tut.first_name|| ','|| tut.last_name ) tutor_name,
( sc.name ) franchise_name,
( prgm.name ) project_name
FROM
fev_oltsession ses
LEFT OUTER JOIN fev_student_oltsession ss ON ses.session_id = ss.session_id
LEFT OUTER JOIN fev_school sc ON ses.school_id = sc.school_id
LEFT OUTER JOIN fev_oltsession_tutor_feedback tf ON ses.session_id = tf.session_id
LEFT OUTER JOIN fev_oltsesson_student_feedback sf ON ses.session_id = sf.session_id
LEFT OUTER JOIN fev_student st ON ss.student_id = st.student_id
LEFT OUTER JOIN fev_tutor_oltsession ts ON ses.session_id = ts.session_id
LEFT OUTER JOIN fev_tutor tut ON ts.tutor_id = tut.tutor_id
LEFT OUTER JOIN fev_program prgm ON ses.program_id = prgm.program_id
WHERE
nvl(sc.is_test_data, 0) != 1
AND
ses.session_id IN(
SELECT
column_value
FROM
TABLE(split_studentsfevtut(nvl(isessionids,'%')))
)
AND ses.status=2
AND (sf.tutor_rating <4)
AND upper(st.first_name
|| ' '
|| st.last_name) LIKE upper(nvl(istudentname, '%'))
AND upper(tut.first_name
|| ' '
|| tut.last_name) LIKE upper(nvl(itutorname, '%'))
AND upper(sc.name) LIKE upper(nvl(ischoolname, '%'))
AND upper(prgm.name) LIKE upper(nvl(iprojectname, '%'))
AND ses.scheduled_starttime >= to_date(ifromdate, 'mm/dd/yyyy hh:mi:ss am')
AND ses.scheduled_starttime <= to_date(itodate, 'mm/dd/yyyy hh:mi:ss am');
END;
Note:All columns are same except 1 that is sf.sessionrating(1st procedure) and sf.tutoringrating(2nd procedure)
