The columns
section of the XML document refer to the columsn that will be offered in the Data Export Manager screens. The text of the column name is arbitrary and can be anything, but the <column column="TABLE.FIELD">
portion must referr to a "core" table of powerschool. When in doubt, use column="STUDENTS.ID"
<columns>
<column column="STUDENTS.ID">Student_Number</column>
<column column="STUDENTS.ID">Enroll_Status</column>
<column column="STUDENTS.ID">Family_Ident</column>
</columns>
The total number of columns returned by the query must match exactly in name and number to the number of column
references in the XML document.
Example:
<queries>
<!--set name here (also applies to permissions_root-->
<query name="com.foo.bar" coreTable="students" flattened="false">
<!--add description here-->
<description>foo example</description>
<!--number of columns here must match number sql returns-->
<columns>
<column column="STUDENTS.ID">Name</column>
<column column="STUDENTS.ID">Date</column>
<column column="STUDENTS.ID">Food</column>
</columns>
<!--SQL query in format <![CDATA[QUERY]]>-->
<sql>
<![CDATA[
select
foo.name as "Name"
foo.date as "Date"
foo.food as "Food"
from foobartable as foo
]]>
</sql>
</query>
</queries>
The coreTable=
value must referr to a known PowerSchool core table. This determines which menu the named query appears in on the Data Export Manager screen. When in doubt, use students
Example:
<query name="com.foo.bar" coreTable="students" flattened="false">
Long query names will result in odd errors and an inability to install and run the Named Query.
Example:
<query name="com.foo.spam_ham_ham_eggs_and_ham" coreTable="students" flattened="false">
Alternative:
<query name="com.foo.spam_ham_2" coreTable="students" flattened="false">
While this is completely valid SQL, PowerQuery can't handle it and direct column references should be used. NB! There is a counter example to this wehre a wildcard must be used when using CTEs. See the errors section below for more information.
Example:
Select *
From table table
When installing the plugin this will result in an error that indicates that the query cannot determine a column name
Alternative:
-- select the first column
Select 1
From table table
When enabling a plugin it will be validated and sometimes kick errors associated with the format of the SQL. Some errors will also manifest when running a plugin from the Data Export Manager screens.
Case 1 - ORDER BY Mismatch
This appears to be due to a column beiing referenced by only the column name in an ORDER BY
clause.
Example:
ORDER BY foobar
Alternative:
ORDER BY spam.foobar
Case 2 - Using CTE Tables
When using CTE joins, you may need to use a SELECT *
rather than a table alias.
Example:
-- CTE use:
LEFT JOIN sca_complete contact1 ON contact1.studentdcid = s.dcid AND contact1.contactprio = 1
LEFT JOIN sca_complete contact2 ON contact2.studentdcid = s.dcid AND contact2.contactprio = 2
WHERE s.enroll_status = 0
ORDER BY s.lastfirst
Alternative:
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
WHERE s.enroll_status = 0
ORDER BY s.lastfirst
Unable to execute the query operation due to an invalid parameter. Update your filter values and try again.
An unexpected error occurred while communicating with the server. Please contact your administrator
These errors tend to be associated with using order by
statements that are valid SQL, but do not refer to selected columns. To resolve this issue:
- Ensure that all
order by
statements in the SQL query are fields that are directly represented in theselect
section. - Entirely remove the
order by
statements -- in some cases this resolves the above error entirely
Example:
select distinct
'enrollment' as "type",
'UPDATE' as "action",
'T_'||teachers.teachernumber as "child_code",
'Instructor' as "role_name",
teachers.homeschoolid as "parent_code"
from TEACHERS TEACHERS
where teachers.status =1
and length(teachers.email_addr) >0
/*
note that the teacher number is not a directly select'd statement.
In this case it is concat'd to 'T_'
*/
order by teachers.teachernumber asc
Alternative:
select distinct
'enrollment' as "type",
'UPDATE' as "action",
'T_'||teachers.teachernumber as "child_code",
'Instructor' as "role_name",
teachers.homeschoolid as "parent_code"
from TEACHERS TEACHERS
where teachers.status =1
and length(teachers.email_addr) >0
/*
homeschoolid is directly select'd
*/
order by teachers.homeschoolid asc