Need help in using LISTAGG

Question

WITH CTE AS (
SELECT
id,
c_no,
TRY_CAST(B.p_no as INTEGER) as p_no
 FROM db_name.schema_name.tbl_1 A
 JOIN db_name.schema_name.tbl_2 B
 ON B.id_col = A.id_col
WHERE flg_col = '0'
AND cd NOT IN ('1','2','3','4')
AND DATE = '2022-02-02'
AND id='12345678'
ORDER BY p_no
)
SELECT
id,
LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no) AS c01
FROM CTE
GROUP BY id;

When I run the above query, I'm getting result AS

Row           ID             C01
1             01            110,118,id_not_found,no_record
2             02            id_found
3             03            no_record

I want to display only the numberic values in C01 column in the result along with the corresponding ID. I want to ignore these 2 values - id_not_found & no_record in the first row.

But in 2nd and 3rd, the value should remain same.

Any suggestions please.

Answer 1

Try to use the TRY_TO_NUMBER function:

WITH CTE AS (
SELECT
id,
c_no,
TRY_CAST(B.p_no as INTEGER) as p_no
 FROM db_name.schema_name.tbl_1 A
 JOIN db_name.schema_name.tbl_2 B
 ON B.id_col = A.id_col
WHERE flg_col = '0'
AND cd NOT IN ('1','2','3','4')
AND DATE = '2022-02-02'
AND id='12345678'
ORDER BY p_no
)
SELECT
id,
IFNULL(LISTAGG(distinct TRY_TO_NUMBER(c_no) , ',') WITHIN GROUP(ORDER BY TRY_TO_NUMBER(c_no)), c_no) AS c01
FROM CTE
GROUP BY id;

Answer 2

The way you have describe it, is if "there are any number of include numbers, other all data is valid"

Using the smallest SQL that demonstrates the code and we do a little preconditioning in a CTE (which could be moved into your CTE)

WITH cte(id, c_no) AS (
    SELECT * FROM VALUES
        (01, '110'),
        (01, '118'),
        (01, 'id_not_found'),
        (01, 'no_record'),
        (02, 'id_found'),
        (03, 'no_record')
), pre_condition AS (
    SELECT *,
        try_to_number(c_no) as c_no_as_num
    FROM cte
)
SELECT
    id,
    count(c_no_as_num) as count_of_nums,
    LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no) AS c01_all,
    LISTAGG (distinct c_no_as_num , ',') WITHIN GROUP (ORDER BY c_no_as_num) AS c01_nums,
    IFF(count_of_nums>0, c01_nums, c01_all) AS c01
FROM pre_condition
GROUP BY id
ORDER BY id;

we get, the answer you want in C01:

ID	COUNT_OF_NUMS	C01_ALL	C01_NUMS	C01
1	2	110,118,id_not_found,no_record	110,118	110,118
2	0	id_found		id_found
3	0	no_record		no_record

so we can mash this smaller:

    SELECT * FROM VALUES
        (01, '110'),
        (01, '118'),
        (01, 'id_not_found'),
        (01, 'no_record'),
        (02, 'id_found'),
        (03, 'no_record')
), pre_condition AS (
    SELECT *,
        try_to_number(c_no) as c_no_as_num
    FROM cte
)
SELECT
    id,
    IFF(count(c_no_as_num)>0, LISTAGG (distinct c_no_as_num , ',') WITHIN GROUP (ORDER BY c_no_as_num), LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no)) AS c01
FROM pre_condition
GROUP BY id
ORDER BY id;

and weave that into your code as:

WITH CTE AS (
    SELECT
        id,
        c_no,
        try_to_number(c_no) as c_no_as_num
        TRY_CAST(B.p_no as INTEGER) as p_no
    FROM db_name.schema_name.tbl_1 A
    JOIN db_name.schema_name.tbl_2 B
        ON B.id_col = A.id_col
    WHERE flg_col = '0'
        AND DATE = '2022-02-02'
        AND id = '12345678'
        AND cd NOT IN ('1','2','3','4')
)
SELECT
    id,
    IFF(count(c_no_as_num)>0
        ,LISTAGG (distinct c_no_as_num , ',') WITHIN GROUP (ORDER BY c_no_as_num)
        ,LISTAGG (distinct c_no , ',') WITHIN GROUP (ORDER BY c_no)
    ) AS c01
FROM cte
GROUP BY id
;