René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Jasper - Datatypes (and such problems)

Turns out that the first row (where r=1) cannot (I repeat: cannot) be selected. At leat not from the iReport GUI.
<?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="Trying to figure out the mysteries and miseries of Java dates. And I disilike it."
  pageWidth="595"
  pageHeight="842"
  columnWidth="535"
  leftMargin="20"
  rightMargin="20"
  topMargin="20"
  bottomMargin="20">


  <parameter name="P_DATE_IN"  class="java.sql.Timestamp" />
  <parameter name="P_DATE_OUT" class="java.sql.Timestamp" />

	<queryString language="SQL">
		<![CDATA[

      select r, p from (

        select  1 r, to_char($P{P_DATE_OUT}, 'dd.mm.yyyy hh24:mi:ss') p, to_date ('28.08.2009 11:11:11', 'dd.mm.yyyy hh24:mi:ss') d from dual union all
        select  2 r, to_char($P{P_DATE_OUT}, 'dd.mm.yyyy hh24:mi:ss') p, to_date ('29.08.2009 00:00:00', 'dd.mm.yyyy hh24:mi:ss') d from dual union all
        select  3 r, to_char($P{P_DATE_OUT}, 'dd.mm.yyyy hh24:mi:ss') p, to_date ('30.08.2009 20:00:00', 'dd.mm.yyyy hh24:mi:ss') d from dual union all

        select 99 r, 'dummy row'                                      p, sysdate                                                  d from dual
      )
      where d = $P{P_DATE_IN}  or
            r = 99 /* Display this row  a l w a y s */

      ]]>
	</queryString>

	<field name="R" class="java.math.BigDecimal"/>
	<field name="P" class="java.lang.String"/>

  <detail>
		<band height="42">

						<textField>
							<reportElement x="0" y="0" width="100" height="14"/>
							<textElement textAlignment='Right'/>
							<textFieldExpression class="java.math.BigDecimal"><![CDATA[$F{R}]]></textFieldExpression>
						</textField>

						<textField>
							<reportElement x="120" y="0" width="100" height="14"/>
							<textElement textAlignment='Right'/>
							<textFieldExpression class="java.lang.String"><![CDATA[$F{P}]]></textFieldExpression>
						</textField>

						<textField>
							<reportElement x="240" y="0" width="100" height="14"/>
							<textElement textAlignment='Right'/>
							<textFieldExpression class="java.sql.Timestamp"><![CDATA[$P{P_DATE_IN}]]></textFieldExpression>
						</textField>

						<textField>
							<reportElement x="360" y="0" width="100" height="14"/>
							<textElement textAlignment='Right'/>
							<textFieldExpression class="java.sql.Timestamp"><![CDATA[$P{P_DATE_OUT}]]></textFieldExpression>
						</textField>

		</band>
	</detail>

</jasperReport>