I'm doing of migrate of Oracle DB to PosgreSQL. I have such a Hierarchical query, like below. I did easier hierarchical queries with success. But I can't do this query. Can Anybody help to make a queries for PostgreSQL?
Oracle query:
select count(*) cnt
from list nl,
parts spl
where nl.month = Month_
and nl.year = Year_
and nl.date_del is null
and nl.id(+) = spl.id
and nl.dep_owner = dep_owner_
and nl.dep_id in ( select d.dep_id
from departments d
connect bt prior d.dep_id = d.parent_id
start with d.dep_id = dep_id_)
connect by prior spl.id = spl.parent_id
start with spl.index = trim(part_index_)
Postgres query:
WITH RECURSIVE cteroot AS (SELECT count(*) AS cnt
FROM parts spl
LEFT JOIN list nl ON spl."ID" = nl."ID"
where nl."MONTH"= Month_
and nl."YEAR" = Year_
and nl."DATE_DEL" is null
and nl."DEP_OWNER" = dep_owner_
and nl."DEP_ID" IN (WITH RECURSIVE cte AS (SELECT "DEP_ID"
FROM departments
where "DEP_ID"= dep_id_
UNION
SELECT d."DEP_ID"
FROM departments d
JOIN cte ON d."PARENT_ID"= cte."DEP_ID")
SELECT "DEP_ID"
FROM cte
)
and spl."INDEX" = TRIM(part_index_)
UNION
SELECT count(*) AS cntd
FROM parts spld
LEFT JOIN list nld ON spl."ID" = nld."ID"
where nld."MONTH"= Month_
and nld."YEAR" = Year_
and nld."DATE_DEL" is null
and nld."DEP_OWNER" = dep_owner_
and nld."DEP_ID" IN (WITH RECURSIVE cted AS (SELECT "DEP_ID"
FROM departments dt
where dt."DEP_ID"= dep_id_
UNION
SELECT td."DEP_ID"
FROM departments td
JOIN cted ON td."PARENT_ID"= cted."DEP_ID")
SELECT "DEP_ID"
FROM cted
)
SELECT count(*) cntv
FROM cteroot)`
This code works but no correct
You are not using a LEFT OUTER JOIN in the Oracle query. Yes, you may use (+) in the join but in the START WITH and CONNECT BY clauses you require spl.index and spl.id to equal another value and that will never be true when an OUTER JOIN has occurred and the values would be NULL so the join can only be an INNER JOIN.
Therefore, your Oracle query is effectively:
SELECT COUNT(*) cnt
FROM list l
INNER JOIN parts p
ON ( l.id = p.id )
WHERE l.month = Month_
AND l.year = Year_
AND l.date_del IS NULL
AND l.dep_owner = dep_owner_
AND l.dep_id IN ( SELECT d.dep_id
FROM departments d
CONNECT BY PRIOR d.dep_id = d.parent_id
START WITH d.dep_id = dep_id_ )
CONNECT BY PRIOR p.id = p.parent_id
START WITH p.index = trim(part_index_)
At the least, you need to change your LEFT JOINs to INNER JOINs.
You also do not join the main recursive sub-query factoring clause (CTE) to itself to make it recursive.
You want something like (untested as you have not shared any sample data):
WITH RECURSIVE depts AS (
SELECT "DEP_ID"
FROM departments
WHERE "DEP_ID"= dep_id_
UNION ALL
SELECT d."DEP_ID"
FROM departments d
INNER JOIN depts p ON d."PARENT_ID" = p."DEP_ID"
),
recursive_parts AS (
SELECT p."ID"
FROM list l
INNER JOIN parts p
ON p."ID" = l."ID"
WHERE l."MONTH" = Month_
AND l."YEAR" = Year_
AND l."DATE_DEL" IS NULL
AND l."DEP_OWNER" = dep_owner_
AND l."DEP_ID" IN ( SELECT "DEP_ID" FROM depts )
AND p."INDEX" = TRIM(part_index_)
UNION ALL
SELECT p."ID"
FROM list l
INNER JOIN parts p
ON p."ID" = l."ID"
INNER JOIN recursive_parts r
ON r."ID" = p."PARENT_ID"
WHERE l."MONTH" = Month_
AND l."YEAR" = Year_
AND l."DATE_DEL" IS NULL
AND l."DEP_OWNER" = dep_owner_
AND l."DEP_ID" IN ( SELECT "DEP_ID" FROM depts )
)
SELECT count(*) cntv
FROM recursive_parts