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
