Example named query file for PowerSchool Clever sync

clever.named_queries

# This is an example only. I no longer use it and hope to update it eventually.
# If you are starting from scratch you should be using the users table not the teachers view
# for creating a PK for your staff. If you don't, teachers who teach at multiple schools will
# not work correctly. My fix was to just keep using the schoolstaff id's (that is what the 
# teacher view uses) but make sure it is always the users homeschool's sschoolstaff id. It'd be
# cleaner to have just used the users table to begin with though. I have a much better understanding
# of mysql and ps tables then I was when I first wrote this.

<queries>
    <query name="org.yourschool.clever.schools.all" coreTable="SCHOOLS" flattened="true">
        <description>Pull school data for clever.</description>
        <args>
        </args>
        <columns>
        <column column="SCHOOLS.SCHOOL_NUMBER">School_id</column>
        <column column="SCHOOLS.NAME">School_name</column>
        <column column="SCHOOLS.SCHOOL_NUMBER">School_number</column>
        <column column="S_IL_SCH_X.RCDTS_HOME_SCHOOL">State_id</column>
        <column column="SCHOOLS.SCHOOL_NUMBER">Nces_id</column>
        <column column="SCHOOLS.LOW_GRADE">Low_grade</column>
        <column column="SCHOOLS.HIGH_GRADE">High_grade</column>
		<column column="SCHOOLS.PRINCIPAL">Principal</column>
		<column column="SCHOOLS.PRINCIPALEMAIL">Principal_email</column>
		<column column="SCHOOLS.SCHOOLADDRESS">School_address</column>
		<column column="SCHOOLS.SCHOOLCITY">School_city</column>
		<column column="SCHOOLS.SCHOOLSTATE">School_state</column>
		<column column="SCHOOLS.SCHOOLZIP">School_zip</column>
		<column column="SCHOOLS.SCHOOLPHONE">School_phone</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select schools.school_number,schools.name,schools.school_number psnumber ,S_IL_SCH_X.RCDTS_HOME_SCHOOL,'' as Nces_id,
				case  
				  when schools.low_grade = 0 then 'Kindergarten'
				  when schools.low_grade < 0 then 'Prekindergarten'
				  when schools.low_grade > 12 then 'Postgraduate'
				  else to_char(schools.low_grade)
				end as lowgrade,
				case 
				  when schools.high_grade = 0 then 'Kindergarten'
				  when schools.high_grade < 0 then 'Prekindergarten'
				  when schools.high_grade > 12 then 'Postgraduate'
				  else to_char(schools.high_grade)
				end as highgrade,
				schools.principal,schools.principalemail,schools.schooladdress,schools.schoolcity,schools.schoolstate,schools.schoolzip,schools.schoolphone from schools
				join S_IL_SCH_X on schools.dcid = S_IL_SCH_X.schoolsdcid
			]]>
        </sql>
    </query>

       <query name="org.yourschool.clever.students.all" coreTable="STUDENTS" flattened="true">
        <description>Pull student data for clever.</description>
        <args>
        </args>
        <columns>
        <column column="STUDENTS.SCHOOLID">School_id</column>
        <column column="STUDENTS.ID">Student_id</column>
        <column column="STUDENTS.STUDENT_NUMBER">Student_number</column>
        <column column="STUDENTS.STATE_STUDENTNUMBER">State_id</column>
        <column column="STUDENTS.LAST_NAME">Last_name</column>
        <column column="STUDENTS.MIDDLE_NAME">Middle_name</column>
        <column column="STUDENTS.FIRST_NAME">First_name</column>
        <column column="STUDENTS.GRADE_LEVEL">Grade</column>
        <column column="STUDENTS.GENDER">Gender</column>
        <column column="STUDENTS.DOB">DOB</column>
        <column column="STUDENTS.ETHNICITY">Race</column>
        <column column="STUDENTS.FEDETHNICITY">Hispanic_Latino</column>
        <column column="S_IL_STU_X.LEP">Ell_status</column>
        <column column="STUDENTS.LUNCHSTATUS">Frl_status</column>
        <column column="S_IL_STU_X.IEP">IEP_status</column>
        <column column="STUDENTS.STREET">Student_street</column>
        <column column="STUDENTS.CITY">Student_city</column>
        <column column="STUDENTS.STATE">Student_state</column>
        <column column="STUDENTS.ZIP">Student_zip</column>
        <column column="PSM_STUDENTCONTACT.EMAIL">Student email</column>
        <column column="STUDENTS.MOTHER">Contact_relationship</column>
        <column column="STUDENTS.MOTHER">Contact_type</column>
        <column column="STUDENTS.MOTHER">Contact_name</column>
        <column column="STUDENTS.HOME_PHONE">Contact_phone</column>
        <column column="STUDENTS.GUARDIANEMAIL">Contact_email</column>
        <column column="PSM_STUDENTCONTACT.EMAIL">Username</column>
        <column column="STUDENTS.STUDENT_NUMBER">Password</column>
 		</columns>
        <sql>
			<![CDATA[
				SELECT
					st.schoolid,
					st. ID,
					st.student_number,
					st.State_StudentNumber,
					st.last_name,
					st.middle_name,
					st.first_name,
					CASE
				WHEN st.grade_level = 0 THEN
					'Kindergarten'
				WHEN st.grade_level < 0 THEN
					'Prekindergarten'
				WHEN st.grade_level > 12 THEN
					'Postgraduate'
				ELSE
					TO_CHAR(st.grade_level)
				END AS Grade,
				 st.gender,
				 TO_CHAR(st.dob, 'mm/dd/yyyy') dob,
				 CASE
				WHEN st.Ethnicity IN('A', 'B', 'I', 'M', 'P', 'W') THEN
					st.Ethnicity
				ELSE
					''
				END AS race,
				 CASE
				WHEN st.FedEthnicity = 1 THEN
					'Y'
				ELSE
					'N'
				END AS Hispanic,
				 CASE
				WHEN S_IL_STU_X.LEP = '01' THEN
					'Y'
				ELSE
					'N'
				END AS ell_status,
				 st.LunchStatus,
				 CASE
				WHEN S_IL_STU_X.IEP = 1 THEN
					'Y'
				ELSE
					'N'
				END AS iep_status,
				 st.street,
				 st.city,
				 'IL' AS state,
				 st.zip,
				(
					SELECT
						psc.email
					FROM
						PSM_STudentcontact psc
					INNER JOIN psm_studentcontacttype psct ON psc.studentcontacttypeid = psct. ID
					AND psct. NAME = 'Self'
					INNER JOIN sync_studentmap ssm ON psc.studentid = ssm.studentid
					INNER JOIN students stu ON ssm.studentsdcid = st.dcid
					AND stu. ID = st. ID
				) stu_email,
				 contacts.contact_relationship AS contact_relationship,
				 contacts.contact_type AS contact_type,
				 contacts.Contact_name AS Contact_name,
				 contacts.Contact_phone AS Contact_phone,
				 contacts.Contact_email AS Contact_email,
				(
					SELECT psc.email FROM PSM_STudentcontact psc 
					INNER JOIN psm_studentcontacttype psct ON psc.studentcontacttypeid = psct.ID AND psct.NAME= 'Self'
					INNER JOIN sync_studentmap ssm ON psc.studentid = ssm.studentid
					INNER JOIN students stu ON ssm.studentsdcid = st.dcid AND stu.ID= st.ID
				) username,
				'changeme' PASSWORD
				FROM students st
				LEFT OUTER JOIN S_IL_STU_X ON st.dcid = S_IL_STU_X.studentsdcid
				LEFT OUTER JOIN U_STUEXT ON st.dcid = U_STUEXT.studentsdcid
				LEFT OUTER JOIN StudentCoreFields ON st.dcid = StudentCoreFields.studentsdcid
				LEFT OUTER JOIN (select
					st.dcid,
					U_STUEXT.P1_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.P1_GENDER = 'F' then 'Female'
					  when U_STUEXT.P1_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					st.mother as Contact_name,
					case 
					  when StudentCoreFields.mother_home_phone is null then StudentCoreFields.motherdayphone
					  else StudentCoreFields.mother_home_phone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.mother is not null
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.P2_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.P2_GENDER = 'F' then 'Female'
					  when U_STUEXT.P2_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					st.father as Contact_name,
					case 
					  when StudentCoreFields.father_home_phone is null then StudentCoreFields.fatherdayphone
					  else StudentCoreFields.father_home_phone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.father is not null
				
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.G3_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.G3_GENDER = 'F' then 'Female'
					  when U_STUEXT.G3_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					U_STUEXT.G3_name as Contact_name,
					case 
					  when U_STUEXT.G3_homephone is null then U_STUEXT.G3_workphone
					  else U_STUEXT.G3_homephone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and U_STUEXT.G3_name is not null
				
				UNION ALL
				
				select
					st.dcid,
					U_STUEXT.G4_RELATIONSHIP || ' - ' || case 
					  when U_STUEXT.G4_GENDER = 'F' then 'Female'
					  when U_STUEXT.G4_GENDER = 'M' then 'Male'
					end as contact_relationship,
					'Parent/Guardian' as contact_type,
					U_STUEXT.G4_name as Contact_name,
					case 
					  when U_STUEXT.G4_homephone is null then U_STUEXT.G4_workphone
					  else U_STUEXT.G4_homephone
					end as Contact_phone,
					st.guardianemail as Contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and U_STUEXT.G4_name is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_1_rel as contact_relationship,
					'Emergency' as contact_type,
					st.emerg_contact_1 as Contact_Name,
					st.Emerg_Phone_1 as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.emerg_contact_1 is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_2_rel as contact_relationship,
					'Emergency' as contact_type,
					st.emerg_contact_2 as Contact_Name,
					st.Emerg_Phone_2 as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and st.emerg_contact_2 is not null
				
				UNION ALL
				
				select
					st.dcid,
					StudentCoreFields.emerg_3_rel as contact_relationship,
					'Emergency' as contact_type,
					StudentCoreFields.emerg_contact_3 as Contact_Name,
					StudentCoreFields.emerg_3_phone as Contact_phone,
					TO_CLOB('') as contact_email
					from students st
					left outer join  U_STUEXT on st.dcid = U_STUEXT.studentsdcid
					left outer join  StudentCoreFields on st.dcid = StudentCoreFields.studentsdcid
				where
					st.enroll_status = 0
					and StudentCoreFields.emerg_contact_3 is not null
				
				) contacts ON st.dcid = contacts.dcid
				WHERE st.enroll_status = 0
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.teachers.all" coreTable="TEACHERS" flattened="true">
        <description>Pull admins for clever</description>
        <args>
        </args>
        <columns>
        <column column="TEACHERS.SCHOOLID">School_id</column>
        <column column="TEACHERS.ID">Teacher_id</column>
        <column column="TEACHERS.TEACHERNUMBER">Teacher_number</column>
        <column column="S_IL_USR_X.IEIN">State_teacher_id</column>
        <column column="TEACHERS.EMAIL_ADDR">Teacher_email</column>
        <column column="TEACHERS.FIRST_NAME">First_name</column>
        <column column="TEACHERS.MIDDLE_NAME">Middle_name</column>
        <column column="TEACHERS.LAST_NAME">Last_name</column>
        <column column="TEACHERS.TITLE">Title</column>
        <column column="TEACHERS.EMAIL_ADDR">Username</column>
        <column column="TEACHERS.HOME_PHONE">Password</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select teachers.schoolid,teachers.id,teachers.teachernumber,
				S_IL_USR_X.IEIN,teachers.email_addr,
				teachers.first_name,teachers.middle_name,teachers.last_name,teachers.title,teachers.email_addr username,teachers.home_phone
				from teachers
				left outer join S_IL_USR_X on teachers.dcid = S_IL_USR_X.usersdcid
				where teachers.status = 1
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.admins.all" coreTable="TEACHERS" flattened="true">
        <description>Pull admins for clever</description>
        <args>
        </args>
        <columns>
        <column column="TEACHERS.HOMESCHOOLID">School_id</column>
        <column column="TEACHERS.DCID">Staff_id</column>
        <column column="TEACHERS.EMAIL_ADDR">Admin_email</column>
        <column column="TEACHERS.FIRST_NAME">First_name</column>
        <column column="TEACHERS.LAST_NAME">Last_name</column>
        <column column="TEACHERS.TITLE">Admin_title</column>
        <column column="TEACHERS.EMAIL_ADDR">Username</column>
        <column column="TEACHERS.HOME_PHONE">Password</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select homeschoolid,dcid,email_addr,first_name,last_name,title,email_addr username,home_phone from teachers
				where psaccess = 1 and groupvalue in (11,9,24,35,40) and status = 1
			]]>
        </sql>
    </query>
       <query name="org.yourschool.clever.sections.all" coreTable="SECTIONS" flattened="true">
        <description>Pull school data for clever.</description>
        <args>
  	    <arg name="termprefix" column="CC.TERMID" type="primitive" required="true" default = "27" />
        </args>
        <columns>
        <column column="SECTIONS.SCHOOLID">School_id</column>
        <column column="SECTIONS.ID">Section_id</column>
        <column column="SECTIONS.TEACHER">Teacher_id</column>
        <column column="SECTIONS.TEACHER">Teacher_2_id</column>
        <column column="SECTIONS.TEACHER">Teacher_3_id</column>
        <column column="SECTIONS.TEACHER">Teacher_4_id</column>
        <column column="SECTIONS.COURSE_NUMBER">Name</column>
        <column column="SECTIONS.SECTION_NUMBER">Section_number</column>
        <column column="SECTIONS.GRADE_LEVEL">Grade</column>
        <column column="COURSES.COURSE_NAME">Course_name</column>
        <column column="SECTIONS.COURSE_NUMBER">Course_number</column>
        <column column="SECTIONS.COURSE_NUMBER">Course_description</column>
        <column column="SECTIONS.EXPRESSION">Period</column>
        <column column="SECTIONS.EXPRESSION">Subject</column>
        <column column="TERMS.NAME">Term_name</column>
        <column column="TERMS.FIRSTDAY">Term_start</column>
        <column column="TERMS.LASTDAY">Term_end</column>
 		</columns>
        <sql>
			<![CDATA[
				select sec.schoolid,
				sec.id,
				sec.teacher,
				(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 1) as teacher2,
					(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 2) as teacher3,
				(select teacherid from (select teacherid, rownum rn from SectionTeacher 
					where sectionteacher.roleid != 21
					and SectionTeacher.sectionid = sec.id
					order by teacherid) where rn = 3) as teacher4,
				'' as sectionname,
				sec.section_number,
				case 
				  when sec.grade_level = 0 then 'Kindergarten'
				  when sec.grade_level < 0 then 'Prekindergarten'
				  when sec.grade_level > 12 then 'Postgraduate'
				  else to_char(sec.grade_level)
				end as gradelevel,
				courses.course_name,
				sec.course_number,
				'' as description,
				sec.expression,
				'' as subject,
				(select name from terms where id = sec.termid and schoolid = sec.schoolid) termname,
				TO_CHAR((select firstday from terms where id = sec.termid and schoolid = sec.schoolid),'YYY-MM-DD') firstday,
				TO_CHAR((select lastday from terms where id = sec.termid and schoolid = sec.schoolid),'YYY-MM-DD') lastday
				from sections sec
				join courses  on courses.course_number = sec.course_number
				where SUBSTR(TO_CHAR(sec.termid),1,2) = :termprefix
			]]>
        </sql>
    </query>
    <query name="org.yourschool.clever.enrollments.all" coreTable="CC" flattened="true">
        <description>Pull active enrollments for sync with clever</description>
        <args>
  	    <arg name="termprefix" column="CC.TERMID" type="primitive" required="true" default = "27" />
        </args>
        <columns>
        <column column="CC.SCHOOLID">School_id</column>
        <column column="CC.SECTIONID">Section_id</column>
        <column column="CC.STUDENTID">Student_id</column>
 		</columns>
        <sql>
	        
			<![CDATA[
				select c.schoolid,c.sectionid,c.studentid from cc c
				join students stu on stu.id = c.studentid
				where stu.enroll_status = 0
				AND SUBSTR(TO_CHAR(c.termid),1,2) = :termprefix
			]]>
        </sql>
    </query>
</queries>

@gchristian

Stumbled over this while trying to resolve a problem in one of our queries. Would you have any idea what might be causing the following PowerQuery error: PowerQuery com.txoof.finalsite cannot determine table name of column studentdcid

We're totally stumped on this one. The lines that are causing trouble are linked here: https://github.com/txoof/PowerQuery/blob/123fa60f2551a412011cef652f556bce97a5931f/FinalSite/queries_root/finalsite.named_queries.xml#L165-L166

Any ideas or help you can offer would be greatly appreciated!

@txoof Yea, i have no idea why its doing that. Sometimes I find the same table won't work as a with but will if i just define it in the join. Your query works if you replace cone and ctwo references to the defined with table with the definitions in place. It makes for very unclean and inefficient code but it works. Also, just doing this works:

			LEFT JOIN (select * from sca_complete) cone ON cone.studentdcid = s.dcid AND cone.contactprio = 1
			LEFT JOIN (select * from sca_complete) ctwo ON ctwo.studentdcid = s.dcid AND ctwo.contactprio = 2

But why? I wish I knew.

@gchristian thanks so much for getting back to us. We eventually stumbled on this solution too.

I've started putting together a gist of dumb problems that I've run into and their solutions here: https://gist.github.com/txoof/d09a808abadcb97240c79c4f22931f78