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

[aciuffo]

@Weg8k 

Can you share your solution that you found for the With...Left Join problem above? We're experiencing exactly the same problem, but have no idea how to work around it.

Thanks!

Aaron

[Weg8k]

@aciuffo So instead of some CTEs at the top of the query, I put them right in the join. For example

with CTE1 as (

 select col1, col2 from tables...

)

select * from othertables

left join CTE1 on...

becomes:

select * from othertables

left join(

     select col1,col2 from tables...

)

on...

I'm not a SQL expert nor a Powerschool query expert, but this has worked multiple times for me now on various queries/CTEs. But it's not always required either, a lot of my Powerqueries have CTEs that do work. 

[aciuffo]

@Weg8k 

Thanks for the speedy response. We eventually stumbled onto this same solution. I've started putting together a gist of these type of things here: https://gist.github.com/txoof/d09a808abadcb97240c79c4f22931f78

If you know any other gems that are worth adding, PRs are always welcome!