Got "ORA-00979 not a group by expression" error for subreport

Question

I'm using JasperSoft Studio 6.5.0 and when I'm previewing a report who has a subreport, it marks an exception, more specifically:

net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Calificaciones.

At first it would seem that the SQL query has an error. However, when I preview the subreport individually, it does display the info incluying the data in the database, so I can't figure out what's wrong.

This is my subreport:

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Calificaciones" pageWidth="570" pageHeight="504" columnWidth="570" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="68d09970-7b06-4119-9606-fbbc390716b0">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="DataAdapter.xml"/>
    <parameter name="ALUMNO" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[12280028]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT *
FROM "CALIFICACION"
WHERE 
     "CALIFICACION"."ALUMNO" = $P!{ALUMNO}]]>
    </queryString>
    <field name="ALUMNO" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="ALUMNO"/>
    </field>
    <field name="MATERIA" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="MATERIA"/>
    </field>
    <field name="SEMESTRE" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="SEMESTRE"/>
    </field>
    <field name="CALIFICACION" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="CALIFICACION"/>
    </field>
    <columnHeader>
        <band height="56" splitType="Stretch">
            <frame>
                <reportElement x="0" y="0" width="570" height="50" uuid="5a55697b-3939-4e25-9b66-046bb30990ca"/>
                <staticText>
                    <reportElement x="10" y="10" width="100" height="30" uuid="1ec69f21-5468-4226-8d53-05c44c4e2b94"/>
                    <textElement>
                        <font size="18"/>
                    </textElement>
                    <text><![CDATA[Materia]]></text>
                </staticText>
                <staticText>
                    <reportElement x="450" y="10" width="100" height="30" uuid="693359ba-2558-478b-abad-574223e78887"/>
                    <textElement>
                        <font size="18"/>
                    </textElement>
                    <text><![CDATA[Semestre]]></text>
                </staticText>
                <staticText>
                    <reportElement x="260" y="10" width="100" height="30" uuid="65817781-bc80-4c85-9893-3fee93e34bd6"/>
                    <textElement>
                        <font size="18"/>
                    </textElement>
                    <text><![CDATA[Calificación]]></text>
                </staticText>
            </frame>
        </band>
    </columnHeader>
    <detail>
        <band height="62" splitType="Stretch">
            <frame>
                <reportElement x="0" y="0" width="570" height="60" uuid="aaaa0932-c998-4f7c-ab73-4c7b20c61564"/>
                <textField>
                    <reportElement x="10" y="10" width="230" height="40" uuid="f1226292-0222-4248-aaef-20a406b7d07c"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{MATERIA}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="260" y="10" width="140" height="40" uuid="c40dd075-90b7-46aa-9a23-d3db059dbe85"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{CALIFICACION}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="450" y="10" width="100" height="40" uuid="e5a25ff6-eddc-4e99-853d-456ef7e103cb"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{SEMESTRE}]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </detail>
</jasperReport>

This is my report

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Subreporte" pageWidth="570" pageHeight="595" columnWidth="570" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" uuid="9372f370-43af-4eba-bc60-91d7b03943d1">
    <property name="com.jaspersoft.studio.data.sql.tables" value=""/>
    <parameter name="ALUMNO" class="java.lang.Integer">
        <defaultValueExpression><![CDATA[12280028]]></defaultValueExpression>
    </parameter>
    <queryString>
        <![CDATA[SELECT *
FROM "ALUMNO"
WHERE ID = $P!{ALUMNO}]]>
    </queryString>
    <field name="ID" class="java.lang.Integer">
        <property name="com.jaspersoft.studio.field.label" value="ID"/>
    </field>
    <field name="NOMBRE" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="NOMBRE"/>
    </field>
    <field name="APELLIDO" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="APELLIDO"/>
    </field>
    <field name="CARRERA" class="java.lang.String">
        <property name="com.jaspersoft.studio.field.label" value="CARRERA"/>
    </field>
    <title>
        <band height="40" splitType="Stretch">
            <staticText>
                <reportElement x="0" y="0" width="150" height="30" uuid="5e78931c-e260-401a-8466-1eab75a44911"/>
                <textElement>
                    <font size="18"/>
                </textElement>
                <text><![CDATA[Datos del alumno]]></text>
            </staticText>
        </band>
    </title>
    <columnHeader>
        <band height="45" splitType="Stretch">
            <frame>
                <reportElement x="0" y="0" width="570" height="40" uuid="f2dc139e-4ea6-4469-817f-504f1370ec71"/>
                <staticText>
                    <reportElement mode="Transparent" x="0" y="7" width="120" height="30" uuid="f47b00e0-0c00-4aff-9c6f-e048d4c83581"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <text><![CDATA[Número de control]]></text>
                </staticText>
                <staticText>
                    <reportElement x="180" y="7" width="120" height="25" uuid="b7659814-96fb-4c44-8545-c11664a480d6"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <text><![CDATA[Nombre completo]]></text>
                </staticText>
                <staticText>
                    <reportElement x="380" y="7" width="100" height="25" uuid="98907558-8463-49f9-af43-659da31bca37"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <text><![CDATA[Carrera]]></text>
                </staticText>
            </frame>
        </band>
    </columnHeader>
    <detail>
        <band height="214" splitType="Stretch">
            <frame>
                <reportElement mode="Opaque" x="0" y="0" width="570" height="103" forecolor="#E1E1E1" backcolor="#E1E1E1" uuid="faf75383-c92f-47bb-987c-a74a58e4d33f"/>
                <textField isBlankWhenNull="true">
                    <reportElement x="0" y="10" width="120" height="30" uuid="9dd8e336-1215-4dd9-b9e4-91382a97321d"/>
                    <textElement>
                        <font size="16"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="180" y="10" width="180" height="30" uuid="e270ce97-b83c-4883-a78c-9c1764d09789"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{NOMBRE}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="180" y="50" width="180" height="30" uuid="dd3d076b-5ced-4c42-a2f2-896058c1f7b2"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{APELLIDO}]]></textFieldExpression>
                </textField>
                <textField>
                    <reportElement x="380" y="10" width="181" height="70" uuid="6f8a6679-c2aa-4e9b-92cc-e165099918a0"/>
                    <textElement>
                        <font size="14"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{CARRERA}]]></textFieldExpression>
                </textField>
                <line>
                    <reportElement positionType="Float" x="0" y="90" width="570" height="1" uuid="4230ed88-1865-41e3-a387-2d007c11a39e"/>
                </line>
            </frame>
            <subreport>
                <reportElement x="0" y="103" width="570" height="107" uuid="6796d833-6825-428a-844f-64b7247dcbe8"/>
                <subreportParameter name="ALUMNO">
                    <subreportParameterExpression><![CDATA[$F{ID}]]></subreportParameterExpression>
                </subreportParameter>
                <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression>
                <subreportExpression><![CDATA["Calificaciones.jasper"]]></subreportExpression>
            </subreport>
        </band>
    </detail>
</jasperReport>

The full stack trace in the console shows the following:

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Calificaciones.
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.fillReport(ReportController.java:550)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController.access$16(ReportController.java:525)
    at com.jaspersoft.studio.editor.preview.view.control.ReportController$1.run(ReportController.java:420)
    at org.eclipse.core.internal.jobs.Worker.run(Worker.java:55)
Caused by: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Calificaciones.
    at net.sf.jasperreports.engine.fill.JRFillSubreport.prepare(JRFillSubreport.java:878)
    at net.sf.jasperreports.engine.fill.JRFillElementContainer.prepareElements(JRFillElementContainer.java:542)
    at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:438)
    at net.sf.jasperreports.engine.fill.JRFillBand.fill(JRFillBand.java:413)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillColumnBand(JRVerticalFiller.java:2536)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillDetail(JRVerticalFiller.java:791)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReportStart(JRVerticalFiller.java:252)
    at net.sf.jasperreports.engine.fill.JRVerticalFiller.fillReport(JRVerticalFiller.java:99)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:609)
    at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFill.run(BaseFillHandle.java:123)
    at java.lang.Thread.run(Thread.java:748)
Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: Calificaciones.
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:347)
    at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1245)
    at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:723)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.setParameters(BaseReportFiller.java:429)
    at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:572)
    at net.sf.jasperreports.engine.fill.BaseReportFiller.fill(BaseReportFiller.java:387)
    at net.sf.jasperreports.engine.fill.JRFillSubreport.fillSubreport(JRFillSubreport.java:736)
    at net.sf.jasperreports.engine.fill.JRSubreportRunnable.run(JRSubreportRunnable.java:59)
    at net.sf.jasperreports.engine.fill.AbstractThreadSubreportRunner.run(AbstractThreadSubreportRunner.java:221)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    ... 1 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: no es una expresión GROUP BY

    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:447)
    at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
    at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:951)
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:513)
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:227)
    at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
    at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:208)
    at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:886)
    at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1175)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1296)
    at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3613)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3657)
    at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1495)
    at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:310)
    ... 11 more

Please [edit] your post to include the COMPLETE stack trace (format as code). There has to be more error information, either in the stack trace or in the log file(s). It is not possible to troubleshoot this without more information. — Jim Garrison, Jan 07 '18 at 05:28
You have an error at query. [ORA-00979: Not a GROUP BY Expression Tips](http://www.dba-oracle.com/t_ora_00979_not_a_group_by_expression.htm) — Alex K, Jan 07 '18 at 15:20
1) Is CALIFICACION a view? 2) Are you sure that your compiled version of subreport (*Calificaciones.jasper*) contains the same query as your posted? 3) Did you try to remove fields properties (`` and so on)? — Alex K, Jan 07 '18 at 15:29

Got "ORA-00979 not a group by expression" error for subreport

0 Answers0