Custom sql fails when creating any application

i have a pretty complex sql statement that I’d like to use as the basis for a grid application (read only).
Scriptcase fails when trying to use it because SC likes to add additional parts to it for an initial count(*) with something called nm_sel_esp etc.
I even tried putting my sql in a prepared statement/function, and tried calling that, it also fails.
Does anyone know how I can force SC to not modify my sql for this use-case of a read-only report?

I’ve seen elsewhere someone said if i convert my subselect toa a join, it may work. I’m hoping for a simplier solution.
Thanks

select p.project_title,
s.session_name,
q.quota_name,
q.quota_minimum::int as minimum_quota,
q.quota_maximum::int as maximum_quota,
tally.qtotal::int as total_booked,
CASE
WHEN q.quota_minimum = q.quota_maximum THEN q.quota_minimum - tally.qtotal – hard quotas
WHEN tally.qtotal >= q.quota_minimum and tally.qtotal <= q.quota_maximum THEN ‘0’ – soft range quotas
WHEN tally.qtotal < q.quota_minimum THEN quota_minimum - tally.qtotal --test min
WHEN tally.qtotal > q.quota_maximum THEN quota_maximum - tally.qtotal --test max
END::int as total_needed
from (
select
p.project_id,
s.session_id,
q.quota_id,
count(sq.quota_id) qtotal
from screener_quotas sq
right join quotas q on sq.quota_id = q.quota_id
left join sessions s on s.session_id = q.session_id
left join projects p on p.project_id = q.project_id
where p.end_date between ‘2020-01-01’ and ‘2020-06-01’

       and p.project_number in ('4999', '5021')
     group by

              p.project_id,

              s.session_id,

              q.quota_id

 ) tally
     left join quotas q on tally.quota_id = q.quota_id

     left join sessions s on s.session_id = q.session_id
     left join projects p on q.project_id = p.project_id

order by p.project_number desc,s.session_name,q.quota_order

Best to create a SQL view/stored procedure and use the view as the basis for your app, if you can. SC is very weird about anything other than simple SQL statements, so let your SQL/MySQL server do that part of the work and you can then present your SC app with a simple SELECT statement. Also, once the view is created, it’s easier to adjust the underlying data for the grid.

Thank you. Ya that is what I ended up doing was creating a view. Afterword I found this great tutorial on using stored procedures (functions in postgres). https://www.youtube.com/watch?v=QUpo1uIgv2I
But it looks like SP can’t be used in Grid app source. (As some of the commenters on that video were wondering too)
Their support still hasn’t replied to me. Nice to know the community is actively engaged :slight_smile: