Query works, but not in jasperReports

[plaagje]

HI there,

Using iReports I've created a report. I'll first explain why I need the query:

A user often has multiple selection criteria. For instance a user likes to see all his open documents (easy), now he wants to only see the documents that are addressed to 5 specific persons. The database often has tens of thousands persons.

The query now will be (simplified):

SELECT *
FROM documents
WHERE addressedto IN (1,2,3) AND
user = 2

But when the user likes to see all documents our program can supply a long string containing ALL 'addressedto' ID's. as you'll understand this probibly ain't the best way. It also can supply a string like "ALL"

So with some help the following query was created:

DECLARE @a VARCHAR(4)
SET @a = 'ALL'

SELECT *
FROM
  verrichting
WHERE
   typeID =
  (CASE WHEN @a <> 'ALL' THEN
    CASE WHEN typeID  IN (@a) THEN typeID
         ELSE NULL
    END
    ELSE typeID
  END)
AND
  user != 2
 

This query works, if I replace ALL with 8,11, I'll only find the types '8,11'
Now i'm using the folling query in ireport:

CREATE TABLE #documents  (addressedto  INT)
INSERT INTO #documents  VALUES(1)
INSERT INTO #documents  VALUES(2)
INSERT INTO #documents  VALUES(3)
INSERT INTO #documents  VALUES(4)

DECLARE @a VARCHAR(4)
SET @a = $P!{TYPE}

SELECT * FROM #documents
WHERE addressedto  =
          (CASE WHEN @a <> 'ALL' THEN
                CASE WHEN addressedto  IN (@a) THEN addressedto
                     ELSE NULL
                END
                ELSE addressedto
           END)

DROP TABLE #documents

$P!{TYPE} is a string with default value "'ALL'" ("ALL" doesn't work)

case 1: If I enter "ALL" everything works
case 2: if I enter 1, only 1 is displayed (also correct)
case 3: if I enter 1,2 an error occurs

case 3 should work (as it does with query browsers)

It would even be better if I could replace the @a with the iReport variable, but this also gives errors...

 

I've attached a ireport and Jasper file for testing purposes. (And I assume this is a jasper report error, not A iReport error)

Code:

Error filling print... Error executing SQL statement for : report1 net.sf.jasperreports.engine.JRException: Error executing SQL statement for : report1      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:143)      at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:686)      at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:606)      at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1277)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:892)      at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:841)      at net.sf.jasperreports.engine.fill.JRFiller.fillReport(JRFiller.java:58)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:417)      at net.sf.jasperreports.engine.JasperFillManager.fillReport(JasperFillManager.java:247)      at com.jaspersoft.ireport.designer.compiler.IReportCompiler.run(IReportCompiler.java:877)      at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:572)      at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:997)  Caused by: java.sql.SQLException: Incorrect syntax near ','.      at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)      at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2754)      at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2195)      at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:620)      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:372)      at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:672)      at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:137)      ... 11 more  Print not filled. Try to use an EmptyDataSource...

[Teodor Danciu]

Hi,

 

What you have is not a query. It is a series of SQL statements and queries. It is a script.

You create tables and insert rows in them.

What we normally call a query is a single SELECT statement. You have more than that and such things cannot be executed with JDBC executeQuery() calls.

 

If you really need to do all that, and a single SELECT is not enough for what you need to achieve, then you need to create a stored procedure on your DB server and call that from the report query.

 

I hope this helps.
Teodor

 

 

 

[plaagje]

Hi Teodord,

Thanks for your reaction. Indeed what I wrote up there isn't just a single command, but thats just so you can test it without having my database. I used multiple queries in iReport, some of them quite complex and all worked. This is the first one I found that worked in my query builder but not in iReport.

Normaly I have my filled database, and if the query below here would work it would be great :).

SELECT *
FROM verrichting
WHERE
   typeID =
  (CASE WHEN $P!{TYPE} <> 'ALL' THEN
    CASE WHEN typeID  IN ($P!{TYPE}) THEN typeID
         ELSE NULL
    END
    ELSE typeID
  END)

(As you don't got these tables I added a little bit of testing script in the openings post)

[plaagje]

Kick :) Anyone got an bright idea of how to make this work ? /tools/fckeditor/editor/images/smiley/msn/embaressed_smile.gif

[plaagje]

After some googling I found a solutions that works:

I've created a user defined function dbo.Split:

---

CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))    
returns @temptable TABLE (items varchar(8000))    
as    
begin    
  declare @idx int    
  declare @slice varchar(8000)    
   
  select @idx = 1    
    if len(@String)<1 or @String is null  return    
   
  while @idx!= 0    
  begin    
    set @idx = charindex(@Delimiter,@String)    
    if @idx!=0    
      set @slice = left(@String,@idx - 1)    
    else    
      set @slice = @String    
   
    if(len(@slice)>0)
      insert into @temptable(Items) values(@slice)    

    set @String = right(@String,len(@String) - @idx)    
    if len(@String) = 0 break    
  end
return    
end

---

DECLARE @a VARCHAR(5000)
SET @a = '$P!{VERRICHTINGSTYPE}'

SELECT *
FROM verrichting
WHERE
typeID   =
          (CASE WHEN @a <> 'ALL' THEN
                CASE WHEN typeID   IN (select items from dbo.split(@a,',')) THEN typeID
                     ELSE NULL
                END
                ELSE typeID
           END)

This works fine, hope this helps others with the same problem.

Post Edited by plaagje at 10/14/2010 13:38