Untitled

--Changes how it's ordered
DROP VIEW IF EXISTS public.view_program_record_line;
CREATE OR REPLACE VIEW public.view_program_record_line
AS
 SELECT  enrollments.id AS enrollment_id,
         examCallEnroll.id AS exam_call_enrollment_id,
         enrollments.program_record_id AS program_record_id,
         subjects.id AS subject_id,
         subjects.code_course AS subject_code,
         subjects.name_course AS subject_name,
         subjects.credits AS subject_credits,
         subProgSubCourse.ordered + 1 AS course,
         trainingPeriod.name AS training_period_name,
         examCall.type_treeLevel1 AS exam_call_type_level1,
         examCallEnroll.state AS exam_call_enroll_state,
		 enrollments.validated AS enrollment_validated,
		 CASE
		 	WHEN enrollments.validated = TRUE
			THEN enrollments.validated_score
			ELSE examCallEnroll.qualification
		 END AS qualification,
         examCall.exam_date AS exam_date
  FROM core_elearning_enrollment enrollments
  INNER JOIN core_elearning_course subjects
  ON subjects.id = enrollments.course_id AND subjects.active_flag = true
  INNER JOIN core_elearning_program_record programRecords
  ON programRecords.id = enrollments.program_record_id AND programRecords.active_flag = true
  LEFT JOIN core_elearning_training_period trainingPeriod
  ON trainingPeriod.id = enrollments.training_period_id AND trainingPeriod.active_flag = true
  INNER JOIN core_elearning_exam_call_enrollment examCallEnroll
  ON examCallEnroll.enrollment_id = enrollments.id AND examCallEnroll.active_flag = true
  LEFT JOIN core_elearning_exam_call examCall
  ON examCall.ID = examCallEnroll.exam_call_id AND enrollments.validated = false AND examCall.active_flag = true
  LEFT JOIN view_subtraining_program_contains_subjects subProgSubCourse
  ON subProgSubCourse.training_program_id = programRecords.training_program_id AND subProgSubCourse.subject_id = subjects.id AND subProgSubCourse.type = 2
  WHERE enrollments.active_flag = true
  AND enrollments.state = 3 --Enrollment validated
  ORDER BY subProgSubCourse.ordered DESC, subjects.name_course, trainingPeriod.name DESC, examCall.exam_date;
  
SELECT * FROM view_program_record_line