title: Oracle SQL Return at least one row
body: <p>I have an Oracle SQL query where I would like to return all rows where a certain column has a value, but if none of the rows have a value for that column then I would like to return one of the blank rows. How can this be accomplished?</p>
  
  <p>My scenario is a learning path with courses that are to completed, but I want to show the percentage completion. Obviously if there are no courses completed I would like to show the learning path with a zero percentage completion.</p>
  
  <p>The query is very complicated so I rather provide a dummy scenario below:</p>
  
  <pre><code>learning_paths
  --------------
  learning_path_id learning_path_name
  1                Oracle Developer
  2                Python Developer
  
  courses
  -------
  course_id course_name
  --------- -----------
  1         Oracle SQL
  2         Oracle PL/SQL
  3         Python
  4         Django
  5         NLTK 
  
  learning_path_items
  -------------------
  learning_path_id course_id 
  ---------------- ---------
  1                1
  1                2
  2                3
  2                4
  2                5
  
  learning_path_enrollments
  -------------------------
  employee_id learning_path_id
  ----------- ----------------
  1           1
  2           2
  3           2
  
  course_enrollments
  ------------------
  employee_id course_id
  ----------- ---------
  2           3
  2           4           
  
  So my results should be:
  employee_id learning_path_id course_id completion
  ----------- ---------------- --------- ----------
  1           1                          0 out of 2
  2           2                3         2 out of 3
  2           2                4         2 out of 3
  3           2                          0 out of 3
  </code></pre>
  
  <p>Below is the actual query, which is very complicated and still a work in progress.</p>
  
  <pre><code>select sub2.director, 
         sub2.cost_centre,
         sub1.employee_number,
         sub1.employee_name,
         sub2.job_title,
         sub1.course_name,
         sub1.learning_path_name,              
         sub2.course_start_date,
         sub2.course_end_date,
         max(sub2.course_end_date) over (partition by sub1.employee_number, sub1.learning_path_name) max_course_end_date,
         sub1.original_date_of_hire,
         sub2.job_start_date promotion_date,
         nvl(sub1.completion_target_days, 9999999999999999) completion_target_days,
         sub1.completion_target_date,
         sub1.no_of_completed_courses,
         sub1.no_of_mandatory_courses,
         round(least(sub1.no_of_completed_courses, sub1.no_of_mandatory_courses) / sub1.no_of_mandatory_courses * 100) completion_percentage       
         --round() time_percentage               
  from   (              
           select papf.employee_number,       
                  nvl(papf.known_as, papf.first_name) || ' ' || papf.last_name employee_name,
                  papf.original_date_of_hire,
                  oav.version_name course_name,
                  olpt.name learning_path_name,
                  ole.completion_target_date completion_target_date,
                  ole.no_of_completed_courses no_of_completed_courses,
                  ole.no_of_mandatory_courses no_of_mandatory_courses,
                  olp.duration completion_target_days,
                  papf.person_id,
                  oav.activity_version_id                
           from   ota_lp_enrollments          ole, 
                  per_all_people_f            papf,
                  ota_learning_paths          olp,
                  ota_learning_paths_tl       olpt,
                  ota_lp_sections             ols,
                  ota_lp_sections_tl          olst,
                  ota_learning_path_members   olpm,
                  ota_activity_versions       oav    -- aka Courses
           where  papf.person_id = ole.person_id         
           and    xxpay_bi_util.get_effective_date(ole.person_id, trunc(sysdate), 'all') between papf.effective_start_date 
                                                                                             and papf.effective_end_date
           and    olp.learning_path_id = ole.learning_path_id
           and    olpt.learning_path_id = ole.learning_path_id
           and    olpt.language = userenv('LANG')
           and    ols.learning_path_id = ole.learning_path_id
           and    olst.learning_path_section_id = ols.learning_path_section_id
           and    olst.language = userenv('LANG')
           and    ols.learning_path_id = ole.learning_path_id
           and    olpm.learning_path_id = ole.learning_path_id
           and    olpm.learning_path_section_id = ols.learning_path_section_id
           and    oav.activity_version_id = olpm.activity_version_id
           group  by papf.employee_number,       
                  nvl(papf.known_as, papf.first_name) || ' ' || papf.last_name,
                  papf.original_date_of_hire,
                  oav.version_name,
                  olpt.name,  
                  olst.name,
                  ole.completion_target_date,
                  ole.no_of_completed_courses,
                  ole.no_of_mandatory_courses,
                  olp.duration,
                  papf.person_id,
                  oav.activity_version_id                
         ) sub1,         
         (
            select erm.*,
                   paaf.assignment_id,
                   paaf.person_id,
                   pj.name job_title,
                   haou.name cost_centre,
                   paafm.job_start_date,
                   xxpay_util.get_lookup_value(trunc(sysdate), 'TRUW_HR_DIRECTORS', hoi.org_information1) director -- 'Derek Kohler (Stores)'
            from   (
                      select odb.booking_id,
                             odb.delegate_person_id,
                             odb.date_booking_placed,
                             oe.course_start_date,
                             oe.course_end_date,
                             oe.course_start_time,
                             oe.course_end_time,
                             oe.enrolment_start_date,
                             oe.public_event_flag,
                             oe.title class_title,
                             oe.activity_version_id
                      from   ota_delegate_bookings       odb,
                             ota_events                  oe   -- aka Classes
                      where  oe.event_id = odb.event_id
                   )  erm,
                   per_all_assignments_f       paaf,
                   per_jobs                    pj,
                   hr_all_organization_units   haou,
                   hr_organization_information hoi,
                   (
                      select paaf.person_id,
                             paaf.assignment_id,
                             paaf.job_id,
                             min(paaf.effective_start_date) job_start_date
                      from   per_all_assignments_f paaf
                      where  paaf.assignment_type in ('E', 'C')
                      and    paaf.primary_flag = 'Y'
                      group  by paaf.person_id,
                             paaf.assignment_id,
                             paaf.job_id
                   )  paafm
            where  paaf.person_id (+) = erm.delegate_person_id
            and    erm.course_start_date between nvl(paaf.effective_start_date (+), to_date('01/01/1000', 'dd/mm/yyyy')) 
                                             and nvl(paaf.effective_end_date   (+), to_date('31/12/4712', 'dd/mm/yyyy'))
            and    paafm.person_id (+) = paaf.person_id
            and    paafm.assignment_id (+) = paaf.assignment_id
            and    paafm.job_id (+) = paaf.job_id
            and    pj.job_id (+) = paaf.job_id
            and    haou.organization_id (+) = paaf.organization_id
            and    hoi.organization_id (+) = paaf.organization_id
            and    hoi.org_information_context (+) = 'TRU_ADD_ORG'                 
         )  sub2   
  where  sub2.activity_version_id (+) = sub1.activity_version_id
  and    sub2.person_id (+) = sub1.person_id
  and    sub1.employee_number in ('2006591', '2005681', '2004118', '2004212') 
  order  by 3, 1, 2, 4, 5, 7
  </code></pre>