Oracle Apex创建视图时SELECT DISTINCT报‘Missing Expression’错误求助

原SQL错误分析 #

1. 主SELECT列中直接嵌套SELECT DISTINCT语句，属于语法错误，单行子查询作为列时不能以完整SELECT块形式存在
2. 视图中使用了页面绑定变量:P16_KODE_KAS，视图是数据库级对象，无法依赖前端页面的会话变量
3. 采用隐式JOIN语法，易产生笛卡尔积；WAJIB_BAYAR字段未指定所属表，存在歧义

修正后的SQL语句 #

方案1：保留明细行，同时展示对应KODE_KAS的统计值 #

CREATE OR REPLACE FORCE EDITIONABLE VIEW "KIR_V_KAS_MASUK_KET" (
    "KODE_KAS", 
    "NIS", 
    "NAMA", 
    "KELAS", 
    "JURUSAN", 
    "PERIODE", 
    "TOTAL_PEMBAYARAN", 
    "TOTAL_WAJIB_BAYAR", 
    "KETERANGAN"
) AS
SELECT
    KMH.KODE_KAS,
    KA.NIS,
    KA.NAMA,
    KA.KELAS,
    KA.JURUSAN,
    KMH.PERIODE,
    -- 窗口函数计算当前KODE_KAS的总付款额
    TO_CHAR(SUM(KMD.PEMBAYARAN) OVER (PARTITION BY KMH.KODE_KAS), '999,999,999') AS TOTAL_PEMBAYARAN,
    -- 基于预计算的活跃人数计算总应付款
    TO_CHAR(KMH.WAJIB_BAYAR ACTIVE_CNT, '999,999,999') AS TOTAL_WAJIB_BAYAR,
    KMD.KETERANGAN
FROM
    KIR_ANGGOTA KA
JOIN KIR_KAS_MASUK_DETAIL KMD 
    ON KA.NIS = KMD.NIS
JOIN KIR_KAS_MASUK_HEAD KMH 
    ON KMD.KODE_KAS = KMH.KODE_KAS
-- 预查询活跃用户总数，避免重复计算
CROSS JOIN (
    SELECT COUNT(*) AS ACTIVE_CNT 
    FROM KIR_ANGGOTA 
    WHERE STATUS != 'TIDAK AKTIF'
) ACTIVE_USERS
WHERE
    KA.STATUS != 'TIDAK AKTIF'
/

方案2：按KODE_KAS聚合统计（若需合并明细行） #

CREATE OR REPLACE FORCE EDITIONABLE VIEW "KIR_V_KAS_MASUK_KET" (
    "KODE_KAS", 
    "PERIODE", 
    "TOTAL_PEMBAYARAN", 
    "TOTAL_WAJIB_BAYAR",
    "DETAIL_NIS",
    "DETAIL_NAMA",
    "DETAIL_KELAS",
    "DETAIL_JURUSAN",
    "DETAIL_KETERANGAN"
) AS
SELECT
    KMH.KODE_KAS,
    KMH.PERIODE,
    TO_CHAR(SUM(KMD.PEMBAYARAN), '999,999,999') AS TOTAL_PEMBAYARAN,
    TO_CHAR(KMH.WAJIB_BAYAR ACTIVE_CNT, '999,999,999') AS TOTAL_WAJIB_BAYAR,
    LISTAGG(KA.NIS, ', ') WITHIN GROUP (ORDER BY KA.NIS) AS DETAIL_NIS,
    LISTAGG(KA.NAMA, ', ') WITHIN GROUP (ORDER BY KA.NAMA) AS DETAIL_NAMA,
    LISTAGG(KA.KELAS, ', ') WITHIN GROUP (ORDER BY KA.KELAS) AS DETAIL_KELAS,
    LISTAGG(KA.JURUSAN, ', ') WITHIN GROUP (ORDER BY KA.JURUSAN) AS DETAIL_JURUSAN,
    LISTAGG(KMD.KETERANGAN, ', ') WITHIN GROUP (ORDER BY KMD.KETERANGAN) AS DETAIL_KETERANGAN
FROM
    KIR_ANGGOTA KA
JOIN KIR_KAS_MASUK_DETAIL KMD 
    ON KA.NIS = KMD.NIS
JOIN KIR_KAS_MASUK_HEAD KMH 
    ON KMD.KODE_KAS = KMH.KODE_KAS
CROSS JOIN (
    SELECT COUNT(*) AS ACTIVE_CNT 
    FROM KIR_ANGGOTA 
    WHERE STATUS != 'TIDAK AKTIF'
) ACTIVE_USERS
WHERE
    KA.STATUS != 'TIDAK AKTIF'
GROUP BY
    KMH.KODE_KAS, KMH.PERIODE, KMH.WAJIB_BAYAR, ACTIVE_CNT
/

关键修正说明 #

• 移除非法嵌套的SELECT DISTINCT块，通过表直接关联获取PERIODE字段
• 替换页面绑定变量，改用表关联和窗口函数/聚合函数实现统计逻辑
• 使用显式JOIN语法明确关联关系，避免笛卡尔积风险
• 通过CROSS JOIN预计算活跃用户总数，减少重复子查询的性能开销
• 明确指定所有字段的所属表，消除歧义

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