我需要帮助使用ListAgg功能

Question

SELECT pid
       ,LISTAGG(DISTINCT acc_no, ',') WITHIN GROUP(ORDER BY acc_no) 
        AS acc_no_txt
FROM
(SELECT pid
      ,CASE WHEN acc_no_v01 IN ('not found','blank','nil-value','',' ','null') THEN 'Acc Nbr Not Enterered'
       ELSE acc_no_v01
       END
       AS acc_no
      ,MIN(TRY_TO_NUMBER(T2.vp_no)) AS vp_no
FROM table_1 T1
JOIN table_2 T2
ON T2.click_stream_integration_id = T1.click_stream_integration_id
WHERE T2.date = '2022-01-01'
AND pid='123456789'
GROUP BY pid,acc_no_v01
ORDER BY TRY_TO_NUMBER(vp_no)
)
GROUP BY pid;

当我运行上述查询时，我会得到如下所述的结果：

PID                          ACC_NO_TXT

123456789              12244059141,Acc Nbr Not Enterered

我想显示结果如下所示：

PID                          ACC_NO_TXT

123456789              12244059141

我不想显示 - ACC NBR不融合

可以帮助我实现这一目标吗？

谢谢！

Answer 1

我不知道他为什么没有将其作为答案，但是您可以做Dean Flinter所说的话：

SELECT pid
       ,LISTAGG(DISTINCT acc_no, ',') WITHIN GROUP(ORDER BY acc_no) 
        AS acc_no_txt
FROM
(SELECT pid
      ,CASE WHEN acc_no_v01 IN ('not found','blank','nil-value','',' ','null') THEN NULL
       ELSE acc_no_v01
       END
       AS acc_no
      ,MIN(TRY_TO_NUMBER(T2.vp_no)) AS vp_no
FROM table_1 T1
JOIN table_2 T2
ON T2.click_stream_integration_id = T1.click_stream_integration_id
WHERE T2.date = '2022-01-01'
AND pid='123456789'
GROUP BY pid,acc_no_v01
ORDER BY TRY_TO_NUMBER(vp_no)
)
GROUP BY pid;