Alternatives to a PIPELINED table function
I am trying to write a piece of code that will display student attendance on Monday through Friday for a given date. I supply the code with the date, it determines the dates for Monday, Tuesday....Friday of that week and then determines which days during the week that student was or wasn't present. My data includes two tables:
Students
-----------
student_id number,
first_name varchar2,
last_name varchar2,
campus_id number
Attendance
-------------
student_id number,
date_of_attendance date
I would like for the output of the tables to be in the following format:
student_id first_name||last_name mon tue wed thu fri
-------------------------------------------------------------------------------
123456 john doe 0 1 0 1 1
163452 jane doe 1 1 1 1 1
1 is if there is a record in the attendance table for that student on that day and 0 is there is not.
I tried doing this using a pipelined table function. Whenever I ran the query, I got the following error:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-801: internal error [hshuid:LU invalid]
06552. 00000 - "PL/SQL: %s"
*Cause:
*Action:
Error at Line: 14 Column: 12
I'm using Oracle 10gXE and according to support note 559786.1 this "should" be due to a bug that results from the type definition including a reserved word. I double-checked and I don't have any such words in my type definition. I tried running the same code in an 11g database and it worked. Unfortunately using 11g isn't an option for this task.
Can anybody suggest an alternative means for doing this?