Plugin will not load due to SQL error

[Weg8k]

Hi all!

Thanks for all the help so far. I had a question about a plugin I am uploading via the plugin management dashboard to grab data from the Incidents tables.

I'm getting an error (filenames changed for privacy):

"myquery.xml: PowerQuery com.myquery.dataset cannot determine table name of column incidentid

My query runs successfully in my local GUI on the Powerschool database tables, but I get this error on upload. Are there specific rules the xml files must follow in regards to joins, column names, etc? 

I am using a few subqueries like below (very, very simplified). 

WITH TableA AS (

           SELECT incident.Incident_ID as IncidentID

          **rest of subquery**

            )

with joins such as: 

LEFT JOIN TableA on Incident_Detail.Incident_ID = TableA.IncidentId

Am I missing something obvious? Or other tips/ideas? We have very similar queries with subqueries and joins without this issue, so I am exceptionally confused! 

[Weg8k]

Update: I successfully got the plugin to load! I reformatted by query and although the results table and logic is exactly the same, Powerschool seems to be happier. However, that has created some new problems:

1. When trying to use as an API call, I receive an error:

   "org.hibernate.exception.SQLGrammarException: could not extract ResultSet"

   Some Googling shows this could be connected to some column names not matching, but I've triple checked and can't find any issues.
2. We also have SFTP set up, so I was going to try to use my query as an export template in that fashion. However, the new query is not showing up in the dropdown to select in the export manager. The rest of my queries from the plugin do show up though!

Perhaps these two are related? My query is still somehow mis-formatted, and thus I can not use it as an export template?

Thanks in advance!

[ParitoshT]

Hi @Weg8k,

Good Day! I recommend working with the PowerSchool SIS administrators in your school district who assisted you with developing this sql as they will be in a better position to guide you further. Meanwhile, you can review our developer's guide and look for similar solutions on PowerSchool Exchange.

Note: You will need to work with the technical contacts in your district to review the information on PowerSource Exchange.

ParitoshT

Community Moderator 

Kudos encourage participation