Jump to content
Changes to the Jaspersoft community edition download ×

If statement for Query...


2005 IR Help

Recommended Posts

By: Michael Bauer - mbabauer

If statement for Query...

2004-08-17 08:22

I bought the book online (which I must say is not all that great), and saw no reference to this, so I thought I would post to see what others have done.

 

I have a report that is getting a couple of Date parameters passed in a Strings, and I need to rig the Query to only add these if they are pressent. Something to the effect of:

"SELECT * FROM table WHERE col="+$P{val}+($p{from} != null ? "From>="+$P{from} : "")

I tried putting this as a <variable> and <variableExpression>, but due to the ordering in the DTD, the SaxParser throws an error. I cannot pass in the SQL statement directly for some other reasons, so I have to find a way to build this internal to the report.

 

 

Thanks in advance...

 

 

 

 

By: Teodor Danciu - teodord

RE: If statement for Query...

2004-08-17 08:41

 

Hi,

 

You need to use the $P!{} syntax inside your query.

And this is explained at page 32 in the guide.

 

Since only parameters can participate in the query,

you'll probably need to use the parameter's

<defaultValueExpression> to place the ?: operators.

 

I hope this helps.

Teodor

 

 

 

 

 

By: Michael Bauer - mbabauer

RE: If statement for Query...

2004-08-17 10:26

Ok, I have tried this as well.

 

My XML snippet:

 

<jasperReport name="DpmoReport" columnCount="1" pageWidth="900" pageHeight="842" columnWidth="635" columnSpacing="4" leftMargin="2" rightMargin="2" topMargin="30" bottomMargin="30">

<reportFont name="Arial_Normal" isDefault="true" fontName="Arial" size="9" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Arial_Bold" isDefault="false" fontName="Arial" size="9" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Bold" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Arial_Italic" isDefault="false" fontName="Arial" size="9" isBold="false" isItalic="true" isUnderline="false" isStrikeThrough="false" pdfFontName="Helvetica-Oblique" pdfEncoding="Cp1252" isPdfEmbedded="false"/>

<reportFont name="Comic_Normal" isDefault="false" fontName="Comic Sans MS" size="10" isBold="false" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="COMIC.TTF" pdfEncoding="Identity-H" isPdfEmbedded="true"/>

<reportFont name="Comic_Bold" isDefault="false" fontName="Comic Sans MS" size="10" isBold="true" isItalic="false" isUnderline="false" isStrikeThrough="false" pdfFontName="COMICBD.TTF" pdfEncoding="Identity-H" isPdfEmbedded="true"/>

<parameter name="From" class="java.lang.String">

</parameter>

<parameter name="To" class="java.lang.String">

</parameter>

<parameter name="Source" class="java.lang.String">

</parameter>

<parameter name="SQL" class="java.lang.String">

<defaultValueExpression>

"SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" + $P{Source} + ($P{From}!=null ? $P{From} : "")

</defaultValueExpression>

</parameter>

<queryString>$P{SQL}</queryString>

...

 

Here is the error I get:

 

13:24:16,810 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:158: ')' expected

13:24:16,810 ERROR [sTDERR] (source unavailable)

13:24:16,810 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:286: ')' expected

13:24:16,825 ERROR [sTDERR] (source unavailable)

13:24:16,825 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:414: ')' expected

13:24:16,825 ERROR [sTDERR] (source unavailable)

13:24:16,825 ERROR [sTDERR] 3 errors

13:24:16,825 ERROR [WebReportingAction] An exception occured: net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:

See error messages above.

net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:

See error messages above.

at net.sf.jasperreports.engine.design.JRAbstractJavaCompiler.compileReport(JRAbstractJavaCompiler.java:155)

at net.sf.jasperreports.engine.design.JRDefaultCompiler.compileReport(JRDefaultCompiler.java:137)

at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:199)

at net.sf.jasperreports.engine.JasperCompileManager.compileReport(JasperCompileManager.java:190)

at net.sf.jasperreports.engine.JasperManager.compileReport(JasperManager.java:976)

at com.bofa.esm.web_reporting.view.util.JasperWebReportComponent.getJasperReport(JasperWebReportComponent.java:282)

at com.bofa.esm.web_reporting.view.util.JasperWebReportComponent.getJasperPrint(JasperWebReportComponent.java:214)

at com.bofa.esm.web_reporting.view.util.JasperWebReportComponent.getHTMLBody(JasperWebReportComponent.java:54)

at com.bofa.esm.web_reporting.view.action.WebReportingAction.reportSelected(WebReportingAction.java:131)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:324)

at org.apache.struts.actions.DispatchAction.dispatchMethod(DispatchAction.java:280)

at org.apache.struts.actions.DispatchAction.execute(DispatchAction.java:216)

at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:484)

at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:274)

at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1482)

at org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:525)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

at javax.servlet.http.HttpServlet.service(HttpServlet.java:810)

at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)

at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)

at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)

at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.jboss.web.tomcat.security.SecurityAssociationValve.invoke(SecurityAssociationValve.java:72)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

at org.jboss.web.tomcat.security.JBossSecurityMgrRealm.invoke(JBossSecurityMgrRealm.java:275)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

at org.apache.catalina.authenticator.SingleSignOn.invoke(SingleSignOn.java:417)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)

at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)

at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)

at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)

at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)

at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)

at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)

at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)

at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)

at java.lang.Thread.run(Thread.java:534)

 

 

 

 

By: Michael Bauer - mbabauer

RE: If statement for Query...

2004-08-17 10:28

Oops, there was more to the error message than I pasted. This is the first part, which has some interesting output:

 

13:27:42,982 DEBUG [simpleObjectCache] Getting Object ID report_2

13:27:42,997 DEBUG [simpleObjectCache] Time to force cache cleanup...

13:27:43,216 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:123: ')' expected

13:27:43,232 ERROR [sTDERR] value = (java.lang.String)("SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" ((java.lang.String)parameter_Source.getValue()) (((java.lang.String)parameter_From.getValue())!=null ? ((java.lang.String)parameter_From.getValue()) : ""));

13:27:43,232 ERROR [sTDERR] ^

13:27:43,232 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:251: ')' expected

13:27:43,247 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:123: ')' expected

13:27:43,247 ERROR [sTDERR] value = (java.lang.String)("SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" ((java.lang.String)parameter_Source.getValue()) (((java.lang.String)parameter_From.getValue())!=null ? ((java.lang.String)parameter_From.getValue()) : ""));

13:27:43,247 ERROR [sTDERR] ^

13:27:43,247 ERROR [sTDERR] C:web_toolsjboss-3.2.5binDpmoReport.java:379: ')' expected

13:27:43,247 ERROR [sTDERR] value = (java.lang.String)("SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" ((java.lang.String)parameter_Source.getValue()) (((java.lang.String)parameter_From.getValue())!=null ? ((java.lang.String)parameter_From.getValue()) : ""));

13:27:43,247 ERROR [sTDERR] ^

13:27:43,263 ERROR [sTDERR] value = (java.lang.String)("SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" ((java.lang.String)parameter_Source.getValue()) (((java.lang.String)parameter_From.getValue())!=null ? ((java.lang.String)parameter_From.getValue()) : ""));

13:27:43,263 ERROR [sTDERR] ^

13:27:43,263 ERROR [sTDERR] 3 errors

13:27:43,263 ERROR [WebReportingAction] An exception occured: net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:

See error messages above.

net.sf.jasperreports.engine.JRException: Errors were encountered when compiling report expressions class file:

See error messages above.

 

 

 

 

By: Teodor Danciu - teodord

RE: If statement for Query...

2004-08-18 04:38

 

Hi,

 

It looks like the + operators are missing in the

SQL parameter default expression.

Make sure you are compiling the exact same file

you are editing.

 

Also, in the <queryString> tag you should put:

 

<queryString>$P!{SQL}</queryString>

 

Note the ! character present there.

 

I hope this helps.

Teodor

 

 

 

 

 

By: Michael Bauer - mbabauer

RE: If statement for Query... (Solution Found

2004-08-18 10:58

The '+' signs are present in the XML file, but for some reason they are being removed when the XML file is converted to Java. Again, my expression is:

 

<defaultValueExpression>

"SELECT * FROM INV4.DIST_STATE WHERE SOURCE_APPLICATION=" + $P{Source} + ($P{From}!=null ? $P{From} : "")

</defaultValueExpression>

 

All plus signs seem to get removed no matter what I do. I tried a couple of experiments, replacing the '+' with <![CDATA[+]]>, replacing it with "+" and some other combinations. The break came when I tried the "+" method, and saw that the parser produced " " as the output. The fix is to use + instead of + (you have to do the same for some other special chars as well).

 

I am still getting some errors, but I will do some more research before I post.

 

 

Thanks,

 

Mike Bauer

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

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 account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...