Hi I am trying to get sessionids from select statement and looping by passing the sessionids in where condition but it is is faling to fetch the data dont know whether the value is passing inside the loop or not
Below is my procedure
The link which i provided below is the screenshot of the sessionids which i am tring to get in intial stage i mean before the loop
https://ibb.co/XXHP83K
Where i am going wrong?
Could you please help me in that?
PROCEDURE Assigned_Tutor_details(
iTutorusername IN VARCHAR,
iStartDate IN VARCHAR,
iendDate IN VARCHAR,
itime IN VARCHAR,
ocursor OUT t_cursor
)
IS
BEGIN
FOR x IN (select olt.course_id As course_id,olt.session_id AS session_id from fev_oltsession olt
INNER JOIN fev_session_workpool_routing wrkpl on olt.session_id = wrkpl.session_id)
LOOP
IF x.course_id IS NOT NULL then
IF(itime is not null) then
OPEN ocursor FOR
SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,crs.name AS course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
LEFT OUTER JOIN fev_course_bucket crb ON olt.course_id = crb.course_Id
LEFT OUTER JOIN fev_course crs ON crb.course_id = crs.course_id
LEFT OUTER JOIN fev_tutorbucket bckt ON crb.bucket_id = bckt.bucket_id AND olt.subject_id = bckt.subject_id
LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
WHERE
nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
AND
wrkpl.scheduled_starttime = to_date(iStartDate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
olt.session_id = x.session_id;
Else
OPEN ocursor FOR
SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,crs.name AS course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
LEFT OUTER JOIN fev_course_bucket crb ON olt.course_id = crb.course_Id
LEFT OUTER JOIN fev_course crs ON crb.course_id = crs.course_id
LEFT OUTER JOIN fev_tutorbucket bckt ON crb.bucket_id = bckt.bucket_id AND olt.subject_id = bckt.subject_id
LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
WHERE
nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
AND
wrkpl.scheduled_starttime >= TO_DATE(
istartdate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
wrkpl.scheduled_starttime < TO_DATE(
ienddate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
olt.session_id = x.session_id;
END IF;
END IF;
IF x.course_id IS NULL then
IF(itime is not null) then
OPEN ocursor FOR
SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,wrkpl.course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
LEFT OUTER JOIN fev_grade grd ON olt.GRADE_ID = grd.GRADE_ID
LEFT OUTER JOIN fev_tutorbucket bckt ON grd.school_level_id = bckt.school_level_id AND olt.subject_id = bckt.subject_id
LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
WHERE
nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
AND
wrkpl.scheduled_starttime = to_date(iStartDate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
olt.session_id = x.session_id;
Else
OPEN ocursor FOR
SELECT wrkpl.session_id,wrkpl.student_id,wrkpl.student_name,wrkpl.preferred_tutor_id,(tut.first_name ||' '||tut.last_name)As Assigned_TUTOR_NAME,lo.user_name,wrkpl.grade_name,sub.name AS subject_name,wrkpl.school_name,dst.district_name,wrkpl.course_name,(scl.title)As School_level,bckt.bucket_id,bckt.title,wrkpl.scheduled_starttime FROM FEV_SESSION_WORKPOOL_ROUTING wrkpl
LEFT OUTER JOIN fev_oltsession olt on wrkpl.session_id = olt.session_id
RIGHT OUTER JOIN FEV_TUTOR tut ON wrkpl.preferred_tutor_id = tut.tutor_id
LEFT OUTER JOIN FEV_LOGIN lo ON tut.login_id = lo.login_id
LEFT OUTER JOIN fev_district dst ON wrkpl.district_id = dst.district_id
LEFT OUTER JOIN fev_grade grd ON olt.GRADE_ID = grd.GRADE_ID
LEFT OUTER JOIN fev_tutorbucket bckt ON grd.school_level_id = bckt.school_level_id AND olt.subject_id = bckt.subject_id
LEFT OUTER JOIN fev_subject sub ON bckt.subject_id = sub.subject_id
LEFT OUTER JOIN fev_school_level scl ON bckt.SCHOOL_LEVEL_ID = scl.SCHOOL_LEVEL_ID
WHERE
nvl(lower(lo.USER_NAME),0) LIKE (nvl(lower(iTutorusername)|| '%','%'))
AND
wrkpl.scheduled_starttime >= TO_DATE(
istartdate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
wrkpl.scheduled_starttime < TO_DATE(
ienddate,
'mm/dd/yyyy hh:mi:ss am'
)
AND
olt.session_id = x.session_id;
END IF;
END IF;
END LOOP;
END;