时间戳

首页 > 解决方案 > 按查询分组 Rising ORA-00934 异常

oracle - 按查询分组 Rising ORA-00934 异常

问题描述

我有以下查询

SELECT
    t.orgname,
    t.phone,
    t.phone2,
    t.fax,
    t.address1,
    t.address2,
    t.address3,
    t.address4,
    t.city,
    t.postal,
    t.glname,
    t.acctvalue,
    t.acctname,
    t.dateacct,
    t.fiscalfrom,
    t.fiscalto,
    t.folio,
    t.piece,
    regexp_replace(coalesce(
        LISTAGG(t.description, ' || ') WITHIN GROUP(
            ORDER BY
                t.acctvalue
        ), ''), '[^[:print:]]', '')
INTO
    description,
    SUM(t.amtacctcr) AS amtacctcr,
    SUM(t.amtacctdr) AS amtacctdr
FROM (

SELECT
    org.name     AS orgname,
    oi.phone,
    oi.phone2,
    oi.fax,
    loc.address1,
    loc.address2,
    loc.address3,
    loc.address4,
    loc.city,
    loc.postal,
    glc.name     AS glname,
    ev.value     AS acctvalue,
    ev.name      AS acctname,
    fa.description,
    fa.dateacct,
    fa.amtacctcr,
    fa.amtacctdr,
  --getfactdocumentno(ad_table_id, record_id)                 AS piece,
    (
        SELECT
            gcs.seqno
        FROM
            gl_category_sequence gcs
        WHERE
            gcs.c_period_id = fa.c_period_id
            AND gcs.ad_table_id = fa.ad_table_id
            AND gcs.record_id = fa.record_id
            AND gcs.gl_category_id = glc.gl_category_id
    ) AS piece,
    fiscalyearforperiod(fa.c_period_id, '01/01/', 'dd/MM/yy') AS fiscalfrom,
    fiscalyearforperiod(fa.c_period_id, '31/12/', 'dd/MM/yy') AS fiscalto,
    p.periodno   AS folio
FROM
    fact_acct        fa
    INNER JOIN c_period         p ON ( fa.c_period_id = p.c_period_id )
    INNER JOIN gl_category      glc ON ( fa.gl_category_id = glc.gl_category_id )
    INNER JOIN c_elementvalue   ev ON ( fa.account_id = ev.c_elementvalue_id )
    INNER JOIN ad_org           org ON ( fa.ad_org_id = org.ad_org_id )
    INNER JOIN ad_orginfo       oi ON ( org.ad_org_id = oi.ad_org_id )
    INNER JOIN c_location       loc ON ( oi.c_location_id = loc.c_location_id )
WHERE
    fa.ad_table_id = 318
AND fa.record_id      = 1454983
AND fa.c_acctschema_id=1000003
ORDER BY
    fa.fact_acct_id

) t
GROUP BY
    t.orgname,
    t.phone,
    t.phone2,
    t.fax,
    t.address1,
    t.address2,
    t.address3,
    t.address4,
    t.city,
    t.postal,
    t.glname,
    t.acctvalue,
    t.acctname,
    t.dateacct,
    t.fiscalfrom,
    t.fiscalto,
    t.folio,
    t.piece
    order
    by t.acctvalue;

我得到了 ora-00934

但正如我在这里看到的那样，我正在尝试的是可能的。例 10.15。

那么我在哪里错了？

标签： oracle

解决方案

我只能看到不正确的代码是该INTO子句。

请从您的查询中删除以下代码并尝试：

INTO
    description,
    SUM(t.amtacctcr) AS amtacctcr,
    SUM(t.amtacctdr) AS amtacctdr

推荐阅读