glezot Posted April 7, 2016 Share Posted April 7, 2016 Hello, i have this error ORA-01843: not a valid month, at the next query but on Sqldeveloper works perfectly, anyone knows how solve it: AND (0=to_number($P{ejecuccion}) OR (( to_timestamp(cnt.CNT_DTIING) BETWEEN to_timestamp($P{fechaInicio}) AND to_timestamp($P{fechaFin}) or nvl(to_timestamp(cnt.CNT_DTIFIN),to_timestamp('01/01/9999','DD/MM/YYYY')) BETWEEN to_timestamp($P{fechaInicio}) AND to_timestamp($P{fechaFin}) or (to_timestamp(cnt.CNT_DTIING) < to_timestamp($P{fechaInicio}) and to_timestamp($P{fechaFin})< nvl(to_timestamp(cnt.CNT_DTIFIN),to_timestamp('01/01/9999','DD/MM/YYYY')))))) Link to comment Share on other sites More sharing options...
mlopez_1 Posted April 7, 2016 Share Posted April 7, 2016 This happens to me too.In my case, occurs for different configuration of Oracle NLS_ parameters in Oracle SQL Developer and JasperServer when connecting to Oracle database.Make a Report Unit that gives you the NLS_* parametres and compare with SQL Developer.In my case, I installed a linux new server and the locale wasn't right, was different, and my sql query was made thinking in other locale.My report unit is the following, you must have to aim against your oracle db:<?xml version="1.0" encoding="UTF-8"?><!-- Created with Jaspersoft Studio version 6.2.1.final using JasperReports Library version 6.2.1 --><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="NLS_SESSION_PARAMETERS" language="groovy" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="fe16e421-61b6-43d8-92f5-4fdea676d419"> <property name="ireport.zoom" value="1.5"/> <property name="ireport.x" value="282"/> <property name="ireport.y" value="0"/> <property name="ireport.jasperserver.reportUnit" value="/MXXI/informes/INF/NLS_SESSION_PARAMETERS"/> <property name="ireport.jasperserver.url" value="https://jaspermxxirepodes.sescam.jclm.es:8443/jasperserver/services/repository/"/> <property name="ireport.jasperserver.report.resource" value="/MXXI/informes/INF/NLS_SESSION_PARAMETERS_files/Main_Jrxml"/> <property name="com.jaspersoft.studio.data.defaultdataadapter" value="MXXI"/> <property name="ireport.jasperserver.user" value="jasperadmin"/> <property name="net.sf.jasperreports.export.xls.sheet.names.hoja1" value="Hoja 1"/> <property name="com.jaspersoft.studio.data.sql.tables" value=""/> <import value="org.apache.commons.lang3.time.DateFormatUtils"/> <parameter name="SR_ID_INTERNO_BD" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[""]]></defaultValueExpression> </parameter> <parameter name="SR_ID_EXTERNO_CR" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA[""]]></defaultValueExpression> </parameter> <parameter name="SR_NOMBRE_LISTADO" class="java.lang.String" isForPrompting="false"> <defaultValueExpression><![CDATA["NLS_SESSION_PARAMETERS"]]></defaultValueExpression> </parameter> <queryString language="SQL"> <![CDATA[sELECT *FROM NLS_SESSION_PARAMETERSORDER BY PARAMETER]]> </queryString> <field name="PARAMETER" class="java.lang.String"/> <field name="VALUE" class="java.lang.String"/> <background> <band splitType="Stretch"/> </background> <pageHeader> <band height="70" splitType="Stretch"> <subreport> <reportElement x="0" y="0" width="555" height="70" uuid="0268c1f0-4e18-4ba8-8b45-6a3e565b235a"/> <subreportParameter name="SR_ID_EXTERNO_CR"> <subreportParameterExpression><![CDATA[$P{SR_ID_EXTERNO_CR}]]></subreportParameterExpression> </subreportParameter> <subreportParameter name="SR_NOMBRE_LISTADO"> <subreportParameterExpression><![CDATA[$P{SR_NOMBRE_LISTADO}]]></subreportParameterExpression> </subreportParameter> <subreportParameter name="SR_ID_INTERNO_BD"> <subreportParameterExpression><![CDATA[$P{SR_ID_INTERNO_BD}]]></subreportParameterExpression> </subreportParameter> <connectionExpression><![CDATA[$P{REPORT_CONNECTION}]]></connectionExpression> <subreportExpression><![CDATA["repo:EncabezadoMXXI_Vertical"]]></subreportExpression> </subreport> </band> </pageHeader> <columnHeader> <band height="26" splitType="Stretch"> <staticText> <reportElement mode="Opaque" x="0" y="16" width="191" height="10" backcolor="#CCCCCC" uuid="371f653a-5287-464b-9eae-16860b7a0c67"/> <box leftPadding="2"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font fontName="Arial" size="8"/> </textElement> <text><![CDATA[PARÁMETRO]]></text> </staticText> <staticText> <reportElement mode="Opaque" x="191" y="16" width="219" height="10" backcolor="#CCCCCC" uuid="eeef23a2-7b53-45da-9de6-697707aac1f6"/> <box leftPadding="2"> <pen lineWidth="0.25"/> <topPen lineWidth="0.25"/> <leftPen lineWidth="0.25"/> <bottomPen lineWidth="0.25"/> <rightPen lineWidth="0.25"/> </box> <textElement> <font fontName="Arial" size="8"/> </textElement> <text><![CDATA[VALOR]]></text> </staticText> </band> </columnHeader> <detail> <band height="15" splitType="Stretch"> <property name="local_mesure_unitheight" value="pixel"/> <property name="com.jaspersoft.studio.unit.height" value="px"/> <textField> <reportElement x="0" y="0" width="191" height="15" uuid="9d10b132-dbea-4024-828d-d04a93deea2e"/> <box leftPadding="2" rightPadding="2"/> <textElement verticalAlignment="Middle"> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression><![CDATA[$F{PARAMETER}]]></textFieldExpression> </textField> <textField isBlankWhenNull="true"> <reportElement x="191" y="0" width="219" height="15" uuid="26d28f99-3d81-4d9e-bbba-70b26e285f27"/> <box leftPadding="2" rightPadding="2"/> <textElement verticalAlignment="Middle"> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression><![CDATA[$F{VALUE}]]></textFieldExpression> </textField> </band> </detail> <pageFooter> <band height="14" splitType="Stretch"> <line> <reportElement x="0" y="-1" width="554" height="1" uuid="6ef2dbeb-2ae0-4e31-8363-93191a984108"/> </line> <textField pattern=""> <reportElement x="0" y="0" width="155" height="14" uuid="42938f14-358e-44a1-a0b8-021ce5509dea"/> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA["Fecha listado: " + new SimpleDateFormat("dd/MM/yyyy HH:mm:ss").format(new java.util.Date())]]></textFieldExpression> </textField> <textField> <reportElement x="434" y="0" width="101" height="14" uuid="89c7282d-4b41-4d9c-8d66-77887db29f1b"/> <textElement textAlignment="Right"> <font size="8"/> </textElement> <textFieldExpression><![CDATA["Página "+$V{PAGE_NUMBER}+" de"]]></textFieldExpression> </textField> <textField evaluationTime="Report"> <reportElement x="535" y="0" width="19" height="14" uuid="f327607e-c9d8-4662-9c4a-a2fc4194f7f3"/> <textElement> <font size="8"/> </textElement> <textFieldExpression><![CDATA[" " + $V{PAGE_NUMBER}]]></textFieldExpression> </textField> </band> </pageFooter> <summary> <band height="16" splitType="Stretch"> <textField evaluationTime="Report"> <reportElement x="14" y="6" width="317" height="10" uuid="adf7a31c-7f31-4469-abdc-565de8d267cd"/> <textElement> <font fontName="Arial" size="8"/> </textElement> <textFieldExpression><![CDATA["TOTAL REGISTROS ENCONTRADOS: " + $V{REPORT_COUNT}]]></textFieldExpression> </textField> </band> </summary></jasperReport> Regads, Mariano Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now