适配SAP HANA与Oracle的STRING_AGG替代方案问询

原SAP HANA SQL使用STRING_AGG实现字符串聚合，但迁移至Oracle时需替换为LISTAGG，而LISTAGG无法在SAP HANA中运行。以下是改造后的兼容版本：

改造核心思路 #

通过判断当前数据库类型，分别调用对应数据库的字符串聚合函数：

• SAP HANA环境下调用STRING_AGG
• Oracle环境下调用LISTAGG（搭配WITHIN GROUP (ORDER BY NULL)，无特定排序需求时使用）

兼容版完整SQL #

CREATE OR REPLACE VIEW VIEW_RADTEST_CLICK_TODAY AS
SELECT 
        ROW_NUMBER() OVER() AS row_num,
        v1.*
FROM 
(
SELECT  v.consult_date,
        v.PATIENT_EMPLOYEE_ID,
        v.PATIENT_KWT_NKWT,
        v.PATIENT_NAME,
        v.DEPARTMENT_ID,
        v.DEPARTMENT_NAME,
        v.DOCTORS_ID,
        v.DOCTOR_NAME,
        v.OFFICE_ID,
        v.OFFICE_NAME,
        COUNT(cl.LABTEST_ID) AS "PATIENT_RADTEST_COUNT",
        -- 兼容HANA和Oracle的字符串聚合：LABTEST_ID
        CASE
            WHEN EXISTS (SELECT 1 FROM M_DATABASE WHERE DATABASE_NAME = 'HDB') THEN
                STRING_AGG(CASE 
                                WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN cl.LABTEST_ID 
                                ELSE '0'
                            END, ',')
            WHEN EXISTS (SELECT 1 FROM V$DATABASE) THEN
                LISTAGG(CASE 
                            WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN cl.LABTEST_ID 
                            ELSE '0'
                        END, ',') WITHIN GROUP (ORDER BY NULL)
        END AS "LABTEST_ID",
        -- 兼容HANA和Oracle的字符串聚合：TEST_NAME
        CASE
            WHEN EXISTS (SELECT 1 FROM M_DATABASE WHERE DATABASE_NAME = 'HDB') THEN
                STRING_AGG(CASE 
                                WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN ts.TEST_NAME 
                                ELSE '0'
                            END, ',')
            WHEN EXISTS (SELECT 1 FROM V$DATABASE) THEN
                LISTAGG(CASE 
                            WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN ts.TEST_NAME 
                            ELSE '0'
                        END, ',') WITHIN GROUP (ORDER BY NULL)
        END AS "TEST_NAME",
        -- 兼容HANA和Oracle的字符串聚合：CATEGORY_ID
        CASE
            WHEN EXISTS (SELECT 1 FROM M_DATABASE WHERE DATABASE_NAME = 'HDB') THEN
                STRING_AGG(CASE 
                                WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN ts.CATEGORY_ID 
                                ELSE '0'
                            END, ',')
            WHEN EXISTS (SELECT 1 FROM V$DATABASE) THEN
                LISTAGG(CASE 
                            WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN ts.CATEGORY_ID 
                            ELSE '0'
                        END, ',') WITHIN GROUP (ORDER BY NULL)
        END AS "CATEGORY_ID",
        -- 兼容HANA和Oracle的字符串聚合：CATEGORY_NAME
        CASE
            WHEN EXISTS (SELECT 1 FROM M_DATABASE WHERE DATABASE_NAME = 'HDB') THEN
                STRING_AGG(CASE 
                                WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN tc.CATEGORY_NAME 
                                ELSE '0'
                            END, ',')
            WHEN EXISTS (SELECT 1 FROM V$DATABASE) THEN
                LISTAGG(CASE 
                            WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN tc.CATEGORY_NAME 
                            ELSE '0'
                        END, ',') WITHIN GROUP (ORDER BY NULL)
        END AS "CATEGORY_NAME"
FROM ECLINIC_KNG.VIEW_DOCTOR_CONSULT_TODAY v
INNER JOIN ECLINIC_KNG.CONSULT_LABTEST cl
    ON(v.CONSULT_ID=cl.CONSULT_ID)
INNER JOIN ECLINIC_KNG.TEST_SETUP ts
    ON(cl.LABTEST_ID=ts.TEST_ID)
INNER JOIN ECLINIC_KNG.TEST_CATEGORY tc
     ON (ts.CATEGORY_ID=tc.CATEGORY_ID)
WHERE v.CONSULT_DATE BETWEEN CURRENT_DATE AND CURRENT_DATE
AND ts.ACTIVE_STATUS ='Y'
AND tc.ACTIVE_STATUS ='Y'
AND ts.LAB_TYPE IN('X','O')
GROUP BY 
        v.consult_date,
        v.PATIENT_EMPLOYEE_ID,
        v.PATIENT_KWT_NKWT,
        v.PATIENT_NAME,
        v.DEPARTMENT_ID,
        v.DEPARTMENT_NAME,
        v.DOCTORS_ID,
        v.DOCTOR_NAME,
        v.OFFICE_ID,
        v.OFFICE_NAME
ORDER BY v.OFFICE_NAME 
)AS v1;

补充说明 #

1. 原SQL中ELSE 0改为ELSE '0'，避免数值与字符串拼接时的类型不一致问题
2. Oracle的LISTAGG强制要求指定WITHIN GROUP子句，此处用ORDER BY NULL表示无特定排序需求
3. 通过查询数据库系统视图M_DATABASE（SAP HANA专属）和V$DATABASE（Oracle专属）判断当前环境，确保对应函数仅在支持的数据库中执行

内容的提问来源于stack exchange，提问作者Luke