I have the below query

SELECT LISTAGG(contract, ',')
   WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
      FROM Site_TAB
      WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'

Output is as shown below

I have another query which is

SELECT contract
                   FROM company_site_cfv
                   WHERE cf$_site_type_db = 'CENTRAL'

Output is as below.

Is there any easy way I could write this so that I could get the final out put as 908,918,901?

Any help is much appreciated

CodePudding user response：

You could try the following:

1. Union of both queries before using LISTAGG

SELECT 
    LISTAGG(contract, ',')
        WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
FROM (
    SELECT contract 
    FROM Site_TAB
    WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'
    UNION ALL
    SELECT contract
    FROM company_site_cfv
    WHERE cf$_site_type_db = 'CENTRAL'
) t

2. Since both queries as shown in the example yield, one record. You could CONCAT the result of a cross join eg

SELECT
    t1."CONTRACT_LIST" || "," || t2.contract AS "CONTRACT_LIST"
FROM
    (
    SELECT LISTAGG(contract, ',')
         WITHIN GROUP (ORDER BY contract) "CONTRACT_LIST"
    FROM Site_TAB
    WHERE C_Ecom_Util_API.Is_Web_DC(contract) = 'TRUE'
) t1
CROSS JOIN (
    SELECT contract
    FROM company_site_cfv
    WHERE cf$_site_type_db = 'CENTRAL'
) t2

Let me know if this works for you.