Oracle 19c替代SAP HANA STRING_AGG的LISTAGG命令适配方案

适配后的Oracle 19c视图创建语句 #

CREATE OR REPLACE VIEW ECLINIC.VIEW_LABTEST_CLICK_ALL_V2 AS
SELECT ROW_NUMBER() OVER(ORDER BY v1.OFFICE_NAME, v1.consult_date) AS row_num,
       v1.*
FROM (
    SELECT  v.consult_date,
            v.consult_id,
            v.PATIENT_EMPLOYEE_ID,
            v.PATIENT_CIVILID,
            --v.PATIENT_KWT_NKWT,
            v.PATIENT_NAME,
            v.DEPARTMENT_NAME,
            v.DEPTNAME_AR,
            v.DOCTOR_NAME,
            v.OFFICE_ID,
            v.OFFICE_NAME,
            v.OFFICENAME_AR,
            COUNT(cl.LABTEST_ID) AS "PATIENT_LABTEST_COUNT",
            LISTAGG(CASE 
                        WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN ts.TEST_NAME 
                        ELSE '0'
                    END, ',') WITHIN GROUP (ORDER BY ts.TEST_NAME) AS "TEST_NAME",
            LISTAGG(CASE 
                        WHEN v.OFFICE_ID IN(4,5,6,7,8) THEN tc.CATEGORY_NAME 
                        ELSE '0'
                    END, ',') WITHIN GROUP (ORDER BY tc.CATEGORY_NAME) AS "CATEGORY_NAME"
    FROM ECLINIC.VIEW_DOCTOR_CONSULT_ALL 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 ts.ACTIVE_STATUS = 'Y'
      AND tc.ACTIVE_STATUS IN('Y','N')
      AND ts.LAB_TYPE IN('L')
    GROUP BY 
        v.consult_date,
        v.consult_id,
        v.PATIENT_EMPLOYEE_ID,
        v.PATIENT_CIVILID,
        --v.PATIENT_KWT_NKWT,
        v.PATIENT_NAME,
        v.DEPARTMENT_NAME,
        v.DEPTNAME_AR,
        v.DOCTOR_NAME,
        v.OFFICE_ID,
        v.OFFICE_NAME,
        v.OFFICENAME_AR
) AS v1
ORDER BY v1.OFFICE_NAME, v1.consult_date;

关键改动说明 #

• 替换STRING_AGG为LISTAGG：Oracle 19c无STRING_AGG函数，使用LISTAGG实现字符串聚合，必须通过WITHIN GROUP (ORDER BY ...)指定聚合排序规则，这里按聚合字段本身排序以保证结果一致性。
• 修正数据类型冲突：原语句ELSE 0为数字类型，与TEST_NAME/CATEGORY_NAME字符串类型不兼容，改为ELSE '0'避免隐式转换错误。
• 调整ORDER BY位置：Oracle不允许GROUP BY子查询内直接使用ORDER BY，将原排序逻辑移至外层查询，同时为ROW_NUMBER()窗口指定排序规则，确保行号生成稳定。
• 规范过滤条件位置：将原JOIN后的过滤条件移至WHERE子句，符合Oracle编码习惯且不影响业务逻辑。

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