I have a complex query that has a lot of joins. It runs slow, but I got around that using a materialized view created from the complex query view. When I needed to add ‘just one more thing’ to the query, it no longer functions. I've tried to improve the performance using CTE but I can't get it to work.
Here's what I am trying to do:
WITH
disburse_amount as
(SELECT
c_disb_document as la_number,
SUM(a_disburse) OVER (PARTITION BY c_disb_document) as sumdisb,
COUNT(c_disb_document) OVER (PARTITION BY c_disb_document) as countdisb
FROM disburse_amount
)
SELECT
--columns list from other joined tables
--columns list from CTE
da.sumdisb as a_disburse,
da.sumdisb as total_disburse
FROM main_table
--CTE join
JOIN disburse_amount da
ON da.la_number = d.c_disb_document
--other joined tables
The errors I keep getting are either telling me that I have to do a UNION ALL with my recursive CTE (I don't think it's recursive?) or it highlights the da.sumdisb and says that it's missing a select statement or something like that.
I am using Oracle db. Can anyone please tell me what I'm doing wrong?
FYI, I also tried the CTE without window functions and using GROUP BY instead, but still would not run.
Regards,
BS