ODBC Custom Fields - Students

To get the data from the custom fields I have used the pvsis_custom_students view.

Here is a query I use in SQL Developer to get the data from the students custom fields:

select s.lastfirst, s.student_number, s.grade_level,
NVL(c.text_value,' ') as CustomField1,
NVL(d.text_value,' ') as CustomField2,
NVL(e.text_value,' ') as CustomField3,
NVL(f.text_value,' ') as CustomField4,
TO_NUMBER(NVL(g.char_value,'0')) as CustomField5
from students s
left outer join pvsis_custom_students c
on s.id = c.studentid and c.field_name = 'CustomField1'
left outer join pvsis_custom_students d
on s.id = d.studentid and d.field_name = 'CustomField2'
left outer join pvsis_custom_students e
on s.id = e.studentid and e.field_name = 'CustomField3'
left outer join pvsis_custom_students f
on s.id = f.studentid and f.field_name = 'CustomField4'
left outer join pvsis_custom_students g
on s.id = g.studentid and g.field_name = 'CustomField5'
where s.schoolid = ~(curschoolid)
and s.enroll_status = 0
order by s.lastfirst

I am gathering 4 character fields and 1 numeric field.
I use the NVL function to convert nulls to characters.
I use the TO_NUMBER function to convert a character field to numeric.
I use a "left outer join" to get all the student records and I must do this join for each custom field I want to extract (note the alias letters used for each custom field / join).

Warning: The more custom fields you extract the slower the query runs.

Hope this helps,
Leslie Stevens
Edmonton Public Schools