时间戳

首页 > 解决方案 > SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式

java - SQLSyntaxErrorException: ORA-00979: 不是 GROUP BY 表达式

问题描述

在 java 中运行此代码但在 SQL Developer 中工作正常时，我收到此错误“不是 GROUP BY 表达式”。不知道我在哪里做错了。任何人请帮助

错误 ：SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression

SELECT data.portfolioid AS "CUSTOMER ID",
       company.name AS "COMPANY",
       trunc(data.startdate, :format) AS "USAGE DATE",
       SUM(data.sessions) AS "SESSIONS",
       SUM(data.zerosessions) AS "ZEROSESSIONS",
       SUM(data.sms) AS "SMS",
       SUM(data.VOICEDURATION) AS "VOICEDURATION",
       SUM(data.totalbytes) AS "TOTAL BYTES",
       SUM(data.billedbytes) AS "NORMALIZED BYTES",
       sim.custom0 AS "CUSTOM1"
FROM
  (SELECT portfolioid,
          context,
          startdate,
          totalbytes,
          billedbytes,
          sessions,
          zerosessions,
          sms,
          voiceduration,
          sim.imsi
   FROM nmma_abc_mo DATA
   INNER JOIN nn_abc sim ON data.imsi = sim.imsi
   WHERE portfolioid IN
       (SELECT pf.id
        FROM nc_vt_abc cb,
             nn_abc pcb,
             nn_abc pf
        WHERE pcb.id = cb.productid
          AND pf.id = pcb.portfolioid
          AND pf.domainid = :domainid
          AND (:portfolioid = -1
               OR pf.id = :portfolioid) )
     AND data.startdate BETWEEN to_date(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND to_date(:endDate, 'YYYY-MM-DD HH24:MI:SS') ) DATA
LEFT JOIN nnp_abc portfolio ON portfolio.id = data.portfolioid
LEFT JOIN nnpc_abc company ON company.id = portfolio.companyid
LEFT JOIN nnsc_v_abc sim ON sim.imsi = data.imsi
WHERE sim.custom0 IS NOT NULL
GROUP BY data.portfolioid,
         company.name,
         trunc(data.startdate, :format),
         sim.custom0
ORDER BY data.portfolioid

标签： javasqldatabaseoracle

解决方案

因此，正如我在评论中提到的，尝试将您的日期格式移动到内部查询，以避免像这样重复的占位符：

SELECT data.portfolioid AS "CUSTOMER ID",
       company.name AS "COMPANY",
       data.usage_date AS "USAGE DATE",
       SUM(data.sessions) AS "SESSIONS",
       SUM(data.zerosessions) AS "ZEROSESSIONS",
       SUM(data.sms) AS "SMS",
       SUM(data.VOICEDURATION) AS "VOICEDURATION",
       SUM(data.totalbytes) AS "TOTAL BYTES",
       SUM(data.billedbytes) AS "NORMALIZED BYTES",
       sim.custom0 AS "CUSTOM1"
FROM   (SELECT portfolioid,
               CONTEXT,
               trunc(startdate, :format) AS USAGE_DATE
               totalbytes,
               billedbytes,
               sessions,
               zerosessions,
               sms,
               voiceduration,
               sim.imsi
        FROM   nmma_abc_mo data
        INNER  JOIN nn_abc sim ON data.imsi = sim.imsi
        WHERE  portfolioid IN (SELECT pf.id
                               FROM   nc_vt_abc cb, nn_abc pcb, nn_abc pf
                               WHERE  pcb.id = cb.productid
                               AND    pf.id = pcb.portfolioid
                               AND    pf.domainid = :domainid
                               AND    (:portfolioid = -1 OR pf.id = :portfolioid))
        AND    data.startdate BETWEEN to_date(:startDate, 'YYYY-MM-DD HH24:MI:SS') AND
               to_date(:endDate, 'YYYY-MM-DD HH24:MI:SS')

        ) data
LEFT   JOIN nnp_abc portfolio ON portfolio.id = data.portfolioid
LEFT   JOIN nnpc_abc company ON company.id = portfolio.companyid
LEFT   JOIN nnsc_v_abc sim ON sim.imsi = data.imsi
WHERE  sim.custom0 IS NOT NULL
GROUP  BY data.portfolioid, company.name, data.usage_date, sim.custom0
ORDER  BY data.portfolioid;

推荐阅读