Inner Joins and the dreaded "dot"

[ktrinad]

By: John Woltman - repugnant

Inner Joins and the dreaded "dot"

2004-07-08 17:34

Short Question:

Is it possible to use INNER JOINs with JasperReports?

 

Long:

I'm evaluating JasperReports for use in my company. I was testing it earlier today, along with the major GUIs (iReport, Open Report Designer, and JasperAssistant).

 

I tried a simple INNER JOIN with two tables, but JasperReports always generated empty pages. I read through the mailing list and forums, and tried aliasing my fields with AS, but it never worked. Here's a sample of the query:

 

SELECT expenses.airfare, expenses.hotel, expenses.emp_id

FROM expenses

INNER JOIN employee ON expenses.emp_id = employees.emp_id;

 

(may be incorrect SQL - I can't remember the exact query). But the query I was using to test with worked correctly in MS Access, which I'm using for evaluation. Our production system is Postgresql. Any insights would be appreciated.

 

 

By: karthika - karthika

RE: Inner Joins and the dreaded "dot"

2004-07-09 03:20

what 's the output

 

 

 

By: Chuck Deal - cdeal

RE: Inner Joins and the dreaded "dot"

2004-07-09 04:57

You need to dig a little deeper. perhaps it is the JDBC driver that is not working properly. Look for exceptions.

 

I use a lot of queries, most of which are WAY more complicated than three fields and one join. Jasper has never failed me (except in the cases of user error :)

 

 

By: John Woltman - repugnant

RE: Inner Joins and the dreaded "dot"

2004-07-09 04:56

The output is a completely empty report. iReport, for instance, says "Document has no pages." All three GUIs give me an empty document warning. Here's

the jrxml as generated by iReport:

 

<?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="myfirstjasper"

 

columnCount="1"

 

printOrder="Vertical"

 

orientation="Portrait"

 

pageWidth="612"

 

pageHeight="792"

 

columnWidth="535"

 

columnSpacing="0"

 

leftMargin="30"

 

rightMargin="30"

 

topMargin="20"

 

bottomMargin="20"

 

whenNoDataType="NoPages"

 

isTitleNewPage="false"

 

isSummaryNewPage="false">

 

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

 

<queryString><![CDATA[sELECT SUM(air) as TotalAir, employees.emp_id as EmpId

FROM expenses

 

INNER JOIN employees

ON expenses.emp_id=employees.emp_id

 

GROUP BY employees.emp_id;]]></queryString>

 

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

 

<field name="EmpId" class="java.lang.Integer"/>

 

<background>

 

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

 

</band>

 

</background>

 

<title>

 

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

 

</band>

 

</title>

 

<pageHeader>

 

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

 

</band>

 

</pageHeader>

 

<columnHeader>

 

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

 

</band>

 

</columnHeader>

 

<detail>

 

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

 

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

 

mode="Opaque"

 

x="122"

 

y="13"

 

width="100"

 

height="18"

 

forecolor="#000000"

 

backcolor="#FFFFFF"

 

key="element-3"

 

stretchType="NoStretch"

 

positionType="FixRelativeToTop"

 

isPrintRepeatedValues="true"

 

isRemoveLineWhenBlank="false"

 

isPrintInFirstWholeBand="false"

 

isPrintWhenDetailOverflows="false"/>

 

<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{TotalAir}]]></textFieldExpression>

 

</textField>

 

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

 

mode="Opaque"

 

x="18"

 

y="13"

 

width="100"

 

height="18"

 

forecolor="#000000"

 

backcolor="#FFFFFF"

 

key="element-4"

 

stretchType="NoStretch"

 

positionType="FixRelativeToTop"

 

isPrintRepeatedValues="true"

 

isRemoveLineWhenBlank="false"

 

isPrintInFirstWholeBand="false"

 

isPrintWhenDetailOverflows="false"/>

 

<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[$F{EmpId}]]></textFieldExpression>

 

</textField>

 

</band>

 

</detail>

 

<columnFooter>

 

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

 

</band>

 

</columnFooter>

 

<pageFooter>

 

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

 

</band>

 

</pageFooter>

 

<summary>

 

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

 

</band>

 

</summary>

 

</jasperReport>

 

 

 

By: Edgar Dollin - edollin

RE: Inner Joins and the dreaded "dot"

2004-07-09 05:59

Is the sql tested elsewhere, i.e. in a command line sql interface?

 

 

By: John Woltman - repugnant

RE: Inner Joins and the dreaded "dot"

2004-07-09 06:39

Yes, the real SQL statement was written using MS Access SQL text editor, and then executed and displayed the correct results.

 

Now that I'm back at work I have the actual SQL here:

SELECT SUM(air), fname, lname, employees.emp_id as EmpId

FROM expenses INNER JOIN employees ON expenses.emp_id=employees.emp_id

GROUP BY employees.emp_id, lname, fname;

 

This query works fine in Access, but not in JasperReports

 

 

By: Chuck Deal - cdeal

RE: Inner Joins and the dreaded "dot"

2004-07-09 06:50

Any exceptions? Does a simple query work? make sure that you alias the SUM() field.

 

 

By: John Woltman - repugnant

RE: Inner Joins and the dreaded "dot"

2004-07-09 19:20

I have tried aliasing the SUM() field, but it did not help. A simple query, like SELECT * FROM employees works fine. I did not see any exceptions but for one that seemed GUI-related.

 

 

By: Edgar Dollin - edollin

RE: Inner Joins and the dreaded "dot"

2004-07-10 08:21

I have found Jasper much more effective by using extending the JRDataSource. You can use the easy one to extend a result set and then extend the getFieldValue method to alias your complex field names.

 

Edgar

 

 

By: John Woltman - repugnant

RE: Inner Joins and the dreaded "dot"

2004-07-10 12:51

I will give that a try, though it seems like a bit of work for such a simple query. Are there any examples of using a query like mine, without a custom JRDataSource?

 

 

By: Daren O - rckrll106

RE: Inner Joins and the dreaded "dot"

2004-07-12 06:10

I have wrote custom datasources for two dimensional object arrays, and array lists (because we do a lot of computations programatically - where result sets aren't enough) with no problem.

 

Therefore, if you created a custom datasource for a result set (based on the idea of mapping generic field names) its a one time write.

 

I suggest you look at the source code for the JRResultSetDataSource and other JRDataSource implementations.

 

Again, you shouldn't have any problems using the JRResultSetDataSource with select * queries. You only have an issue when it is when you have tableName.ColumnName in the select.

 

 

 

 

By: Daren O - rckrll106

RE: Inner Joins and the dreaded "dot"

2004-07-09 08:32

Based on my experience with Jasper is that it does not support the dot notation. It has been a problem for sometime (since 0.5.0) and hasn't been fixed as far as I know.

 

I think one of the problems (based on my experience) is that databases return metadata differently. Jasper uses the column names to get the data in a JRResultSetDataSource. Meaning if you have employees.emp_id in your select your database might not return emp_id in the metadata. Some databases will return employees.emp_id or emp_id[1], etc.

 

This is important to the application we are writing because we want to be database independent (as much as possible). So what we've done is to create reports with generic names like Field1, Field2, etc.. and map them to a new datasource we've created. I.E. Field1 would be the result sets column 1, etc...

 

I'm not saying this is the best solution, but it works for us. One thing I've found out is Jasper is really flexible, and there has always been a work around or a fix for any problem I've encountered.

 

Don't give up on Jasper based on the dot notation!! It is a great tool.

 

 

 

 

By: Chuck Deal - cdeal

RE: Inner Joins and the dreaded "dot"

2004-07-09 09:07

That is a really good point about the metadata values.

 

Have you verified that that is what is happening in this case?

 

Working on your idea about using the coulumn number instead, I wonder if Jasper could make the definition of the Field tags "order-dependant" so that you define your fields in the order they would be in the sql statement. Then as a failsafe, the JRResultsetDatasource could do an extra check, like so:

 

.

.

.

else if (clazz.equals(java.lang.Float.class))

{

objValue = resultSet.getString(field.getName());

if(resultSet.wasNull())

{

objValue = resultSet.getString(field.getColumnNumber());

if (resultSet.wasNull())

{

objValue = null;

}

else

{

objValue = new Float((String)objValue);

}

}

else

{

objValue = new Float((String)objValue);

}

}

.

.

.

 

I'm just tossing that out there for comment...

 

 

By: John Woltman - repugnant

RE: Inner Joins and the dreaded "dot"

2004-07-13 08:05

This whole thread is a mistake, I'm sorry to say. There were two versions of my database, a populated and a partially populated one. I was referencing the partially populated one in the ODBC data source path, which caused my report generation to fail.

 

Sorry for the inconvenience :(