JRException: Unknown Column Name

[2004 IR Help]

By: Lanurd - llittle

JRException: Unknown Column Name

2005-07-14 12:02

I am converting some Oracle Reports to iReports/JasperReports and have been stuck on this error all day.

 

If I add the field as a column I get an error 'not in group by'.

 

I've included the JRXML file

 

<?xml version="1.0" encoding="UTF-8" ?>

<!-- Created with iReport - A designer for JasperReports -->

<!DOCTYPE jasperReport PUBLIC "//JasperReports//DTD Report Design//EN" "http://jasperreports.sourceforge.net/dtds/jasperreport.dtd">

<jasperReport

name="testing2"

columnCount="1"

printOrder="Vertical"

orientation="Portrait"

pageWidth="595"

pageHeight="842"

columnWidth="535"

columnSpacing="0"

leftMargin="30"

rightMargin="30"

topMargin="20"

bottomMargin="20"

whenNoDataType="NoPages"

isTitleNewPage="false"

isSummaryNewPage="false">

<property name="ireport.scriptlethandling" value="0" />

<import value="java.util.*" />

<import value="net.sf.jasperreports.engine.*" />

<import value="net.sf.jasperreports.engine.data.*" />

<parameter name="P_dt" isForPrompting="true" class="java.lang.String">

<parameterDescription><![CDATA[From Date]]></parameterDescription>

</parameter>

<parameter name="Q_dt" isForPrompting="true" class="java.lang.String">

<parameterDescription><![CDATA[To Date]]></parameterDescription>

</parameter>

<queryString><![CDATA[select a.site_no, a.employee_id, count(decode(b.sku_no, '7419', a.appt_dt))Prebook, count(decode(b.sku_no, '7420', a.appt_dt))Refer from tmx.appointment a, tmx.appointment_dtl b, tmx.site c where a.site_no = b.site_no and a.appt_dt = b.appt_dt and a.seq_no = b.seq_no and a.site_no = c.site_no and upper(c.name) = 'CW' and a.appt_dt between to_date('01012005','MM/DD/YYYY') and to_date('07012005','MM/DD/YYYY') and b.sku_no in ('7419','7420') and a.status_cd = 'PAID' group by a.site_no, a.employee_id]]></queryString>

<field name="SITE_NO" class="java.lang.Double"/>

<field name="APPT_DT" class="java.sql.Timestamp"/>

<field name="SEQ_NO" class="java.lang.Double"/>

<field name="EMPLOYEE_ID" class="java.lang.String"/>

<field name="ORIGINAL_EMPLOYEE_ID" class="java.lang.String"/>

<field name="START_TIME" class="java.lang.Double"/>

<field name="END_TIME" class="java.lang.Double"/>

<field name="CUSTOMER_NAME" class="java.lang.String"/>

<field name="CUSTOMER_ID" class="java.lang.String"/>

<field name="ARRIVAL_TIME" class="java.sql.Timestamp"/>

<field name="REQUESTED_EMPLOYEE_ID" class="java.lang.String"/>

<field name="GROUP_NO" class="java.lang.Double"/>

<field name="STATUS_CD" class="java.lang.String"/>

<field name="PREBOOK_FL" class="java.lang.String"/>

<field name="TYPE_CD" class="java.lang.String"/>

<field name="NOTES" class="java.lang.String"/>

<field name="DATE_CREATED" class="java.sql.Timestamp"/>

<field name="USER_CREATED" class="java.lang.String"/>

<field name="DATE_MODIFIED" class="java.sql.Timestamp"/>

<field name="USER_MODIFIED" class="java.lang.String"/>

<field name="CONFIRM_BY" class="java.lang.String"/>

<field name="CONFIRM_DT" class="java.sql.Timestamp"/>

<field name="COUNTRY_CD" class="java.lang.Double"/>

<field name="AREA_CD" class="java.lang.Double"/>

<field name="PHONE_NO" class="java.lang.Double"/>

<field name="TRAN_NO" class="java.lang.Double"/>

<field name="REG_NO" class="java.lang.Double"/>

<field name="BARCODE_NUMBER" class="java.lang.String"/>

<variable name="test" class="java.lang.String" resetType="None" calculation="Nothing">

<variableExpression><![CDATA["test"]]></variableExpression>

<initialValueExpression><![CDATA["test" ]]></initialValueExpression>

</variable>

<variable name="Prebook" class="java.lang.Integer" resetType="Report" calculation="Count">

</variable>

<variable name="Refer" class="java.lang.Integer" resetType="Report" calculation="Count">

</variable>

<background>

<band height="0" isSplitAllowed="true" >

</band>

</background>

<title>

<band height="50" isSplitAllowed="true" >

<staticText>

<reportElement

mode="Transparent"

x="61"

y="5"

width="412"

height="40"

forecolor="#000000"

backcolor="#FFFFFF"

key="staticText"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Center" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Times-Roman" pdfFontName="Times-Roman" size="30" isBold="true" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

</textElement>

<text><![CDATA[Classic Report Template]]></text>

</staticText>

<line direction="TopDown">

<reportElement

mode="Opaque"

x="0"

y="48"

width="534"

height="0"

forecolor="#000000"

backcolor="#FFFFFF"

key="line"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="2Point" fill="Solid" />

</line>

<line direction="TopDown">

<reportElement

mode="Opaque"

x="0"

y="3"

width="534"

height="0"

forecolor="#000000"

backcolor="#FFFFFF"

key="line"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="2Point" fill="Solid" />

</line>

</band>

</title>

<pageHeader>

<band height="9" isSplitAllowed="true" >

</band>

</pageHeader>

<columnHeader>

<band height="20" isSplitAllowed="true" >

<rectangle radius="0" >

<reportElement

mode="Opaque"

x="1"

y="1"

width="534"

height="17"

forecolor="#000000"

backcolor="#999999"

key="element-22"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" />

</rectangle>

<line direction="BottomUp">

<reportElement

mode="Opaque"

x="0"

y="0"

width="535"

height="0"

forecolor="#000000"

backcolor="#FFFFFF"

key="element-88"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" />

</line>

<line direction="BottomUp">

<reportElement

mode="Opaque"

x="0"

y="19"

width="535"

height="0"

forecolor="#000000"

backcolor="#FFFFFF"

key="element-89"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="Thin" fill="Solid" />

</line>

</band>

</columnHeader>

<detail>

<band height="24" isSplitAllowed="true" >

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Opaque"

x="0"

y="4"

width="100"

height="12"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.Double"><![CDATA[$F{SITE_NO}]]></textFieldExpression>

</textField>

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Opaque"

x="107"

y="4"

width="100"

height="20"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA[$F{EMPLOYEE_ID}]]></textFieldExpression>

</textField>

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Opaque"

x="274"

y="4"

width="100"

height="20"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.Integer"><![CDATA[$V{Prebook}]]></textFieldExpression>

</textField>

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Opaque"

x="394"

y="4"

width="100"

height="20"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Arial" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="Cp1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.Integer"><![CDATA[$V{Refer}]]></textFieldExpression>

</textField>

</band>

</detail>

<columnFooter>

<band height="0" isSplitAllowed="true" >

</band>

</columnFooter>

<pageFooter>

<band height="27" isSplitAllowed="true" >

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Transparent"

x="325"

y="4"

width="174"

height="19"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Right" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Helvetica" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA["Page " + $V{PAGE_NUMBER} + " of "]]></textFieldExpression>

</textField>

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Report" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Transparent"

x="499"

y="4"

width="36"

height="19"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Helvetica" pdfFontName="Helvetica" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.lang.String"><![CDATA["" + $V{PAGE_NUMBER}]]></textFieldExpression>

</textField>

<line direction="TopDown">

<reportElement

mode="Opaque"

x="0"

y="1"

width="535"

height="0"

forecolor="#000000"

backcolor="#FFFFFF"

key="line"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<graphicElement stretchType="NoStretch" pen="2Point" fill="Solid" />

</line>

<textField isStretchWithOverflow="false" pattern="" isBlankWhenNull="false" evaluationTime="Now" hyperlinkType="None" hyperlinkTarget="Self" >

<reportElement

mode="Transparent"

x="1"

y="6"

width="209"

height="19"

forecolor="#000000"

backcolor="#FFFFFF"

key="textField"

stretchType="NoStretch"

positionType="FixRelativeToTop"

isPrintRepeatedValues="true"

isRemoveLineWhenBlank="false"

isPrintInFirstWholeBand="false"

isPrintWhenDetailOverflows="false"/>

<box topBorder="None" topBorderColor="#000000" leftBorder="None" leftBorderColor="#000000" rightBorder="None" rightBorderColor="#000000" bottomBorder="None" bottomBorderColor="#000000"/>

<textElement textAlignment="Left" verticalAlignment="Top" rotation="None" lineSpacing="Single">

<font fontName="Times-Roman" pdfFontName="Times-Roman" size="10" isBold="false" isItalic="false" isUnderline="false" isPdfEmbedded ="false" pdfEncoding ="CP1252" isStrikeThrough="false" />

</textElement>

<textFieldExpression class="java.util.Date"><![CDATA[new Date()]]></textFieldExpression>

</textField>

</band>

</pageFooter>

<summary>

<band height="0" isSplitAllowed="true" >

</band>

</summary>

</jasperReport>

 

 

 

 

 

 

By: Teodor Danciu - teodord

RE: JRException: Unknown Column Name

2005-07-28 05:21

 

Hi,

 

For each declared report field you have to have a column

in the query result set.

If not, then you have to remove that report field

or reconsider the entire query.

 

I hope this helps.

Teodor