Untitled

From Walloping Cockroach, 6 Years ago, written in Plain Text.

URL http://geopaste.scratchbook.ch/view/90b7e980

Embed

[3:42 PM] Kayidapuram, Deepak

```sql.get_classes_for_district_with_sorting = WITH class_base AS (SELECT * FROM (SELECT r.*, ROWNUM RNUM, COUNT(*) OVER () RESULT_COUNT FROM (SELECT m.class_id AS CLASS_ID, csdm.district_id AS DISTRICT_ID, m.org_id AS ORG_ID, m.class_name AS CLASS_NAME, m.class_code AS CLASS_CODE, m.course_title AS COURSE_TITLE, m.course_code AS COURSE_CODE, m.isdeleted AS ISDELETED, m.school_year AS SCHOOL_YEAR, m.session_name AS SESSION_NAME, m.session_type AS SESSION_TYPE, m.class_begin_date AS CLASS_BEGIN_DATE, m.class_end_date AS CLASS_END_DATE, m.subjects AS SUBJECTS, m.class_type AS CLASS_TYPE, m.grades AS GRADES, m.sis_source_id AS SIS_SOURCE_ID, m.roster_source AS ROSTER_SOURCE, m.is_contineo AS IS_CONTINEO FROM cms_class_master m, cms_school_district_mapping csdm WHERE csdm.school_id= m.org_id and :whereClauseString ) R ) ) SELECT n.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY :sortOrder) ROWNO, b.* FROM (SELECT a.rnum, a.class_id, COUNT (a.rnum) OVER()RESULT_COUNT, LISTAGG (ccp.added_by, ',') WITHIN GROUP (ORDER BY ccp.product_id) ADDED_BY, DISTRICT_ID, ORG_ID, CLASS_NAME, CLASS_CODE, COURSE_TITLE, COURSE_CODE, a.isdeleted, SCHOOL_YEAR, SESSION_NAME, SESSION_TYPE, CLASS_BEGIN_DATE, CLASS_END_DATE, TEACHER_IDS, SUBJECTS, CLASS_TYPE, GRADES, SIS_SOURCE_ID, ROSTER_SOURCE, IS_CONTINEO, LISTAGG (ccp.product_id, ',') WITHIN GROUP ( ORDER BY ccp.product_id) PRODUCT_IDS FROM (SELECT cb.rnum, cb.class_id, COUNT (cb.rnum) OVER() AS RESULT_COUNT, MIN (cb.district_id ) AS DISTRICT_ID, MIN (cb.org_id) AS ORG_ID, MIN (cb.class_name) AS CLASS_NAME, MIN (cb.class_code) AS CLASS_CODE, MIN (cb.course_title) AS COURSE_TITLE, MIN (cb.course_code) AS COURSE_CODE, MIN (cb.isdeleted) AS ISDELETED, MIN (cb.is_contineo) as is_contineo, MIN (cb.school_year) AS SCHOOL_YEAR, MIN (cb.session_name) AS SESSION_NAME, MIN (cb.session_type) AS SESSION_TYPE, MIN (cb.class_begin_date) AS CLASS_BEGIN_DATE, MIN (cb.class_end_date) AS CLASS_END_DATE, MIN (cb.subjects) AS SUBJECTS, MIN (cb.class_type) AS CLASS_TYPE, MIN (cb.grades) AS GRADES, MIN (cb.sis_source_id) AS SIS_SOURCE_ID, MIN (cb.roster_source) AS ROSTER_SOURCE, LISTAGG (cct.teacher_rumba_id, ',') WITHIN GROUP ( ORDER BY cct.teacher_rumba_id) TEACHER_IDS FROM class_base cb, cms_class_teacher cct WHERE cb.class_id = cct.class_id(+) :whereTeacherIdClauseString GROUP BY cb.class_id, rnum, cb.RESULT_COUNT ORDER BY rnum ) a, cms_class_product ccp WHERE a.class_id = ccp.class_id(+) AND ccp.isdeleted(+) = 'N' GROUP BY a.rnum, a.class_id, DISTRICT_ID, ORG_ID, CLASS_NAME, CLASS_CODE, COURSE_TITLE, COURSE_CODE, a.ISDELETED, SCHOOL_YEAR, SESSION_NAME, SESSION_TYPE, CLASS_BEGIN_DATE, CLASS_END_DATE, TEACHER_IDS, SUBJECTS, CLASS_TYPE, GRADES, SIS_SOURCE_ID, ROSTER_SOURCE, is_contineo ORDER BY RNUM ) b ) n WHERE n.ROWNO BETWEEN :start AND :end

Author

Title

Language

Your paste - Paste your paste here

[3:42 PM] Kayidapuram, Deepak

```sql.get_classes_for_district_with_sorting = WITH class_base AS (SELECT * FROM (SELECT r.*, ROWNUM RNUM, COUNT(*) OVER () RESULT_COUNT FROM (SELECT m.class_id AS CLASS_ID, csdm.district_id AS DISTRICT_ID, m.org_id AS ORG_ID, m.class_name AS CLASS_NAME, m.class_code AS CLASS_CODE, m.course_title AS COURSE_TITLE, m.course_code AS COURSE_CODE, m.isdeleted AS ISDELETED, m.school_year AS SCHOOL_YEAR, m.session_name AS SESSION_NAME, m.session_type AS SESSION_TYPE, m.class_begin_date AS CLASS_BEGIN_DATE, m.class_end_date AS CLASS_END_DATE, m.subjects AS SUBJECTS, m.class_type AS CLASS_TYPE, m.grades AS GRADES, m.sis_source_id AS SIS_SOURCE_ID, m.roster_source AS ROSTER_SOURCE, m.is_contineo AS IS_CONTINEO FROM cms_class_master m, cms_school_district_mapping csdm WHERE csdm.school_id= m.org_id and :whereClauseString ) R ) ) SELECT n.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY :sortOrder) ROWNO, b.* FROM (SELECT a.rnum, a.class_id, COUNT (a.rnum) OVER()RESULT_COUNT, LISTAGG (ccp.added_by, ',') WITHIN GROUP (ORDER BY ccp.product_id) ADDED_BY, DISTRICT_ID, ORG_ID, CLASS_NAME, CLASS_CODE, COURSE_TITLE, COURSE_CODE, a.isdeleted, SCHOOL_YEAR, SESSION_NAME, SESSION_TYPE, CLASS_BEGIN_DATE, CLASS_END_DATE, TEACHER_IDS, SUBJECTS, CLASS_TYPE, GRADES, SIS_SOURCE_ID, ROSTER_SOURCE, IS_CONTINEO, LISTAGG (ccp.product_id, ',') WITHIN GROUP ( ORDER BY ccp.product_id) PRODUCT_IDS FROM (SELECT cb.rnum, cb.class_id, COUNT (cb.rnum) OVER() AS RESULT_COUNT, MIN (cb.district_id ) AS DISTRICT_ID, MIN (cb.org_id) AS ORG_ID, MIN (cb.class_name) AS CLASS_NAME, MIN (cb.class_code) AS CLASS_CODE, MIN (cb.course_title) AS COURSE_TITLE, MIN (cb.course_code) AS COURSE_CODE, MIN (cb.isdeleted) AS ISDELETED, MIN (cb.is_contineo) as is_contineo, MIN (cb.school_year) AS SCHOOL_YEAR, MIN (cb.session_name) AS SESSION_NAME, MIN (cb.session_type) AS SESSION_TYPE, MIN (cb.class_begin_date) AS CLASS_BEGIN_DATE, MIN (cb.class_end_date) AS CLASS_END_DATE, MIN (cb.subjects) AS SUBJECTS, MIN (cb.class_type) AS CLASS_TYPE, MIN (cb.grades) AS GRADES, MIN (cb.sis_source_id) AS SIS_SOURCE_ID, MIN (cb.roster_source) AS ROSTER_SOURCE, LISTAGG (cct.teacher_rumba_id, ',') WITHIN GROUP ( ORDER BY cct.teacher_rumba_id) TEACHER_IDS FROM class_base cb, cms_class_teacher cct WHERE cb.class_id = cct.class_id(+) :whereTeacherIdClauseString GROUP BY cb.class_id, rnum, cb.RESULT_COUNT ORDER BY rnum ) a, cms_class_product ccp WHERE a.class_id = ccp.class_id(+) AND ccp.isdeleted(+) = 'N' GROUP BY a.rnum, a.class_id, DISTRICT_ID, ORG_ID, CLASS_NAME, CLASS_CODE, COURSE_TITLE, COURSE_CODE, a.ISDELETED, SCHOOL_YEAR, SESSION_NAME, SESSION_TYPE, CLASS_BEGIN_DATE, CLASS_END_DATE, TEACHER_IDS, SUBJECTS, CLASS_TYPE, GRADES, SIS_SOURCE_ID, ROSTER_SOURCE, is_contineo ORDER BY RNUM ) b ) n WHERE n.ROWNO BETWEEN :start AND :end

Create Shorturl - Create a shorter url that redirects to your paste?

Private - Private paste aren't shown in recent listings.

Delete After - When should we delete your paste?

Reply to "Untitled"