Return the parameter value if no data is returned for that value?

Question

My report in SSRS accepts multiple studentids. The user wants the report to return the studentid (in it's own row) even if the query doesn't return any data I.E. the ID is invalid. So if the user enters 10 studentids, and 5 of them are invalid, return the data for the 5 "good" IDS, and return 5 rows where the first column would be the invalid ID, and the second column would have an "invalid ID" message.

I tried to UNION with the multiselect parameter and with a compiled JOINed parameter, but I can't figure out how to get each invalid ID on its own row.

SELECT ODSMGR.STUDENT_COURSE.ID, 
   ODSMGR.STUDENT_COURSE.Name, 
   ODSMGR.ACADEMIC_STUDY.STUDENT_LEVEL, 
   SUM(ODSMGR.STUDENT_COURSE.COURSE_CREDITS) CREDITS,
   NVL(SUM(AMOUNT),0) Fees,
   ODSMGR.PERSON_DETAIL.EMAIL_PREFERRED_ADDRESS,
   EMPLOYEE_POSITION_OSU.SUPERVISOR_NAME,
   EMPLOYEE_POSITION_OSU.SUPERVISOR_ID,
   MST_PERSON.EMAIL_PREFERRED_ADDRESS SupervisorEmail
FROM ODSMGR.STUDENT_COURSE 
INNER JOIN ODSMGR.ACADEMIC_STUDY
   ON ODSMGR.STUDENT_COURSE.PERSON_UID = ODSMGR.ACADEMIC_STUDY.PERSON_UID 
   AND ODSMGR.STUDENT_COURSE.ACADEMIC_PERIOD = ODSMGR.ACADEMIC_STUDY.ACADEMIC_PERIOD 
INNER JOIN ODSMGR.PERSON_DETAIL
   ON ODSMGR.STUDENT_COURSE.PERSON_UID = ODSMGR.PERSON_DETAIL.PERSON_UID  
LEFT OUTER JOIN AR_DETAIL_OSU 
   ON AR_DETAIL_OSU.PIDM = ODSMGR.STUDENT_COURSE.PERSON_UID
   AND TERM_CODE = ODSMGR.STUDENT_COURSE.ACADEMIC_PERIOD
   AND detail_code = 'INC1'
LEFT OUTER JOIN EMPLOYEE_POSITION_OSU
   ON EMPLOYEE_POSITION_OSU.PERSON_UID = ODSMGR.STUDENT_COURSE.PERSON_UID
   AND EMPLOYEE_POSITION_OSU.POSITION_STATUS = 'A'
LEFT OUTER JOIN MST_PERSON 
   ON MST_PERSON.PERSON_UID = EMPLOYEE_POSITION_OSU.SUPERVISOR_ID

WHERE ODSMGR.STUDENT_COURSE.PERSON_UID = :PIDMParameter
   and ODSMGR.STUDENT_COURSE.ACADEMIC_PERIOD = :AcademicPeriodCodeParameter
   and COURSE_REGISTER_IND = 'Y'
   and WITHDRAWN_IND = 'N'
   and NVL(ODSMGR.PERSON_DETAIL.CONFIDENTIALITY_IND,'N') = 'N'
GROUP BY    ODSMGR.STUDENT_COURSE.ID, 
        ODSMGR.STUDENT_COURSE.Name, 
        ODSMGR.ACADEMIC_STUDY.STUDENT_LEVEL,
        ODSMGR.PERSON_DETAIL.EMAIL_PREFERRED_ADDRESS,
        EMPLOYEE_POSITION_OSU.SUPERVISOR_NAME,
        EMPLOYEE_POSITION_OSU.SUPERVISOR_ID,
        MST_PERSON.EMAIL_PREFERRED_ADDRESS

The above code is just the basic code. I have no idea how to get it to return the parms if there is no data. :PIDMParameter could be any number of IDs.

Answer 1

Assumeing that your IDs are integers, I would create a table of all IDs and then use that as the main table that your current query is JOINed to.

;WITH IDS AS  
    (  
        SELECT 1 AS PERSON_UID
        UNION ALL  
        SELECT I.PERSON_UID + 1 
        FROM IDS I
        WHERE I.PERSON_UID < 99999  
)

SELECT PERSON_UID 
INTO #PERSON_UIDs
FROM IDS 
WHERE PERSON_UID IN (4, 7, 999)
OPTION(MAXRECURSION 0)

Then LEFT JOIN the temp table to the Student Course table. You'd need to use LEFT JOINs so that the IDs from the #PERSON_IDS temp table show up even when the ID doesn't exists in other tables.

SELECT ... 
FROM #PERSON_UIDs ID 
LEFT JOIN ODSMGR.STUDENT_COURSE SC ON ID.PERSON_UID = SC.PERSON_UID 
...

I just realized that this is what @Harry suggested - though Oracle has recursive CTEs since 11g.2 so I think this should work though I haven't worked with Oracle in years.

Recursion In Oracle