Untitled
aunknown
sql
2 years ago
2.3 kB
6
Indexable
SELECT rca.name as IssueCategory, case when dateFrom is null and dateTo is null then (SELECT count(i.*) FROM vw_dash_laporan_issue_proyek i WHERE i.issue_category = rca.name) else (SELECT count(i.*) FROM vw_dash_laporan_issue_proyek i WHERE i.issue_category = rca.name AND i.created_at::text BETWEEN $1 AND $2) end::integer as totalProject, case when dateFrom is null and dateTo is null then COALESCE((select jsonb_agg( jsonb_build_object( 'issue_name', i.issue_name, 'code_proyek', i.code_proyek, 'name_proyek', i.name_proyek, 'issue_category', i.issue_category, 'issue_status', i.issue_status, 'impacts', i.impacts, 'response_plans', i.response_plans, 'created_at', i.created_at, 'updated_at', i.updated_at ) ) res FROM vw_dash_laporan_issue_proyek i WHERE (lower(i.issue_name) like '%' || lower($3) || '%' or lower(i.code_proyek) like '%' || lower($3) || '%' or lower(i.name_proyek) like '%' || lower($3) || '%' or lower(i.issue_category) like '%' || lower($3) || '%' or lower(i.issue_status) like '%' || lower($3) || '%' or lower(i.impacts) like '%' || lower($3) || '%' or lower(i.response_plans) like '%' || lower($3) || '%') AND i.issue_category = rca.name), '{}'::jsonb) else COALESCE((select jsonb_agg( jsonb_build_object( 'issue_name', i.issue_name, 'code_proyek', i.code_proyek, 'name_proyek', i.name_proyek, 'issue_category', i.issue_category, 'issue_status', i.issue_status, 'impacts', i.impacts, 'response_plans', i.response_plans, 'created_at', i.created_at, 'updated_at', i.updated_at ) ) res FROM vw_dash_laporan_issue_proyek i WHERE (lower(i.issue_name) like '%' || lower($3) || '%' or lower(i.code_proyek) like '%' || lower($3) || '%' or lower(i.name_proyek) like '%' || lower($3) || '%' or lower(i.issue_category) like '%' || lower($3) || '%' or lower(i.issue_status) like '%' || lower($3) || '%' or lower(i.impacts) like '%' || lower($3) || '%' or lower(i.response_plans) like '%' || lower($3) || '%') AND i.issue_category = rca.name AND i.created_at::text BETWEEN $1 AND $2 ), '{}'::jsonb) end as dataProject FROM risk_categories rca
Editor is loading...