Oracle Json对象DISTINCT不断获得重复列

Question

所以我有一个查询，它需要返回一个JSON对象，但无论我尝试什么，DISTINCT都不起作用。我一直在尝试一系列其他测试，无论"WBS"总是显示3个或更多的重复列。有人有什么想法吗?

我在Asp.net 6 MVC工作

PROCEDURE GET_BASELINE_RPT (in_WBS_LEVEL_ID IN NUMBER, in_FISCAL_YEAR IN VARCHAR2, 
in_FISCAL_MONTH IN VARCHAR2, RET OUT CLOB) AS
BEGIN
WITH cte AS (
SELECT /*+MATERIALIZE*/ DISTINCT L.WBS_LEVEL_ID
FROM
WBS_LEVEL L
)
SELECT
JSON_ARRAYAGG (
JSON_OBJECT (
'WBS' VALUE L.WBS_LEVEL_NAME,
'Title' VALUE W.DESCRIPTION,
'Rev' VALUE B.REV_NUMBER,
'ScopeStatus' VALUE W.STATUS,
'BCP' VALUE CASE WHEN BC.FISCAL_YEAR = 0 THEN '' ELSE
SUBSTR(BC.FISCAL_YEAR,3,2)||'-'||LPAD(BC.BCP_FISCAL_ID, 3, '0') END,
'BCPApprovalDate' VALUE BC.APPROVAL_DATE,
'Manager' VALUE P1.NICK_NAME,
'ProjectControlManager' VALUE P2.NICK_NAME,
'ProjectControlEngineer' VALUE P3.NICK_NAME,
'FiscalYear' VALUE W.FISCAL_YEAR,
'FiscalMonth' VALUE W.FISCAL_MONTH,
'WBSNumber' VALUE L.WBS_LEVEL_ID
)RETURNING CLOB)
INTO RET
FROM WBS_LEVEL L
LEFT OUTER JOIN BASELINE_RPT B ON L.WBS_LEVEL_ID = B.WBS_LEVEL_ID
JOIN BCP BC ON BC.BCP_ID = B.BCP_ID
LEFT OUTER JOIN WBS_TREE_MOD W ON L.WBS_LEVEL_ID  = W.WBS_LEVEL_ID
LEFT OUTER JOIN VW_SITEPEOPLE P1 ON W.WBS_MANAGER_SNUMBER = P1.SNUMBER
LEFT OUTER JOIN VW_SITEPEOPLE P2 ON W.PCM_SNUMBER = P2.SNUMBER
LEFT OUTER JOIN VW_SITEPEOPLE P3 ON W.PCE_SNUMBER = P3.SNUMBER
ORDER BY L.WBS_LEVEL_NAME, B.REV_NUMBER DESC;
END GET_BASELINE_RPT;

Answer 1

所以结果是我根本没有得到重复。数据是有差异的，但有太多的列有相同的数据，我没有注意到差异，直到另一次审查。我会尝试重新启动我的查询，但说实话，我可能只是在我的c#中放入一个过滤器。