iReport 3.0.0 and SQL IN Statement

[apnoe1]

Hi,

i am not able to create a sql statement with IN.

i am using iReport 3.0.0  and i have defined a Parameter "lieferscheine" as "java.util.collection"

My report query looks like the following:

 

select id, lieferschein, name as material, nummer as material_id, eu_bezeichnung, eu_nummer, beschreibung as einheit_lang, me_einheit as einheit, bezeichnung as baustelle, baustellennummer as baustelle_id,
eingang, ausgang, richtung, case spezial is true when true then 0 when false then differenz end as differenz, differenz as differenz_orig, kundennummer, akt_firmenname, akt_firmenname2, strasse, hausnummer, plz, ort, freibau, abbau, keine_fracht, mwst, nettopreis, bruttopreis, nettogesamt, mwstgesamt, bruttogesamt, erstellt_am, spezial, case anzahl_stueck is null when true then 0 when false then anzahl_stueck end as anzahl_stueck
from view_lieferschein
where lieferschein <> 0
and ueberpruefung_ok = true
and kundennummer = $P!{kundennummer}
and id IN ($P!{lieferscheine})
order by baustellennummer

 

This query is not working, if i execute the report in ireport, ireports prompts for all the parameters.

$P!{kundennummer} is an integer, i am using e.g.104

$P!{lieferscheine} is a collection, i am using e.g. 14,15,16,17

This is the error i get, in my opinion the query ireport is generating, is not working.

But i can not get the query, ireport generates. is this possible?

could you give me a hint what is going wrong?

 

Fehler beim Füllen... Error executing SQL statement for : Rechnungen_Storniert
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Rechnungen_Storniert
      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:141)     
      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:656)
      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:588)
      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1196)
      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:833)     
      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:782)     
      at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:63)     
      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:402)     
      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:234)     
      at it.businesslogic.ireport.IReportCompiler.run(IReportCompiler.java:947)     
      at java.lang.Thread.run(Unknown Source) 
      Caused by: org.postgresql.util.PSQLException: FEHLER: Syntaxfehler bei »[«     
      at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1592)     
      at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1327)    
      at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:192)     
      at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:451)     
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:350)     
      at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:254)     
      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:135)      ... 10 more 

 

The xml representation of the report is:

 

    <parameter name="kundennummer" isForPrompting="true" class="java.lang.Integer"/>
    <parameter name="rechnungsnummer" isForPrompting="true" class="java.lang.Long"/>
    <parameter name="rechnungsdatum" isForPrompting="true" class="java.lang.String"/>
    <parameter name="skontotext" isForPrompting="true" class="java.lang.String"/>
    <parameter name="lieferscheine" isForPrompting="true" class="java.util.Collection"/>
    <queryString><![CDATA[select id, lieferschein, name as material, nummer as material_id, eu_bezeichnung, eu_nummer, beschreibung as einheit_lang, me_einheit as einheit, bezeichnung as baustelle, baustellennummer as baustelle_id,
eingang, ausgang, richtung, case spezial is true when true then 0 when false then differenz end as differenz, differenz as differenz_orig, kundennummer, akt_firmenname, akt_firmenname2, strasse, hausnummer, plz, ort, freibau, abbau, keine_fracht, mwst, nettopreis, bruttopreis, nettogesamt, mwstgesamt, bruttogesamt, erstellt_am, spezial, case anzahl_stueck is null when true then 0 when false then anzahl_stueck end as anzahl_stueck
from view_lieferschein
where lieferschein <> 0
and ueberpruefung_ok = true
and kundennummer = $P!{kundennummer}
and id IN ($P!{lieferscheine})
order by baustellennummer]]></queryString>

 

 

 

 Kind Regards

Markus

Post Edited by Markus Schlautmann at 02/10/09 22:57

[river_ants]

hi markus,

i solved this by using String instead of Collection,

...change $P!{lieferscheine} as a string not a collection.

hope this one works!

-Anton

 

 

[mdahlman]

Markus,

 

It sounds like you need the $X syntax. Check out these posts:

 

http://jasperforge.org/plugins/espforum/view.php?group_id=102&forumid=103&topicid=23795
http://jasperforge.org/plugins/espforum/view.php?group_id=83&forumid=101&topicid=34266

 

Please let us know if this solves the problem for you.

 

Regards,
Matt