studio prompts for begin date but it ends up ? in query on server

[patricia.williams]

i think this problem is related to type casting.  I prompt for a date and it is a string, $P{start}.  I want to use it in my subquery where the query has something like  

where $P!{CNameClause} and (c.contribution_dt between $P{Start} and $P{End})

If I push it to the server and run it so I can find the sql output, I have where c.contribution_name like 'vander%' and (c.contribution_dt between ? and ?) causing an exception

Is there a way to define a new $P{DateBegin} say as a sql.date and convert $P{Start}?  What would the $P{DateBegin} look like in the expression viewer?

[hozawa]

Are you using the same database with your Studio and Server?　What's the exception you're getting? "between" with String should be OK with Oracle and MySQL.

[patricia.williams]

the database is postgres, and it is the same for studio 6.1  and jasperserver 6.0, when I did preview in studio I got

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRRuntimeException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for: ContributionsByContribName_ContributorsFiler_1441227044181_117244.

So I pushed it over to the server to see what that meant and see in my browser

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ContributionsByContribName_ContributorsFiler_1441227207148_917879

and this in the jasperserver.log

net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ContributionsByContribName_ContributorsFiler_1441227207148_917879

[patricia.williams]

woops, here is the jasperlog

2015-09-02 15:53:45,538 DEBUG JRJdbcQueryExecuter,ContributionsByContribName subreports #1:396 - Parameter #1 (Start of type java.lang.String): 2013-01-01
2015-09-02 15:53:45,539 DEBUG JRJdbcQueryExecuter,ContributionsByContribName subreports #1:396 - Parameter #2 (End of type java.lang.String): 2015-12-31
2015-09-02 15:53:45,542 ERROR JRFillSubreport,pool-9-thread-106:840 - Fill 1: exception
net.sf.jasperreports.engine.JRException: Error executing SQL statement for : ContributionsByContribName_ContributorsFiler_1441227207148_917879

 

maybe i should also add that studio puts up this error when I do the read fields inside the dataset query dialog:

operator does not exist: date >=character varying

No operator matches the given name & argument type.

You might need explicit type casts.

 

[zh3ntil]

Hi,

Add the $P{Start} and $P{End} parameters to report and run it to see the parameters have the correct value. It seems somehow filters don't feed your parameters. If you are using another dataset from the main dataset, you have to pass parameters to your dataset that you are using.

[patricia.williams]

they are in the report,  main has StartDate,EndDate parameters, table subreport has Start, End parameters, table has dataset parameters mapped with Start->StartDate,  End->EndDate.  The main query is select 1 as dummy.  The table query  from jrxml is

 

  <queryString>
   <![CDATA[select *
from (
select c.contribution_amount, to_char(c.contribution_dt,'mm-dd-yyyy') as date,
c.contribution_descr, c.report_info_id,
p.name_last, p.name_first,
concat(p.name_last, concat(' ,',p.name_first)) as name,p.name_organization,persent_type_cd,
p.employer,p.occupation,p.job_title,
a.city, a.state_cd, a.postal_code,
r.report_info_ident, r.filer_info_id,r.report_type_cd
from contribution_info c
inner join contribution_persent cp on  c.contribution_info_id=cp.contribution_info_id
inner join persent_info p on cp.persent_info_id=p.persent_info_id
inner join persent_address pa on p.persent_info_id=pa.persent_info_id
inner join address_info a on pa.address_info_id=a.address_info_id
inner join report_info r on c.report_info_id=r.report_info_id
where 1=1
and $P!{CNameClause}
and (c.contribution_dt between $P{Start} and $P{End})  ) A
INNER JOIN (
select f1.filer_ident,f1.filer_info_id as FILER_ID, fp1.filer_persent_kind_cd,
p1.name_last as FILER_LAST, p1.name_first as FILER_FIRST,p1.name_organization as FILER_ENTITY, p1.persent_type_cd as FILER_TYPE
from filer_info f1
inner join filer_persent fp1 on f1.filer_info_id=fp1.filer_info_id
inner join persent_info p1 on fp1.persent_info_id=p1.persent_info_id
where fp1.filer_persent_kind_cd='FILER') B
 on A.filer_info_id = B.FILER_ID]]>
  </queryString>

 

and again here is the jasperlog complaining about the type

 2015-09-02 15:53:45,538 DEBUG JRJdbcQueryExecuter,ContributionsByContribName subreports #1:396 - Parameter #1 (Start of type java.lang.String): 2013-01-01
2015-09-02 15:53:45,539 DEBUG JRJdbcQueryExecuter,ContributionsByContribName subreports #1:396 - Parameter #2 (End of type java.lang.String): 2015-12-31

[zh3ntil]

Hi Patricia,

I still think that your parameter can't be filled which are used in subreport. Give default values to subreport table dataset parameters like "2013-01-01" and "2013-12-31". Run the report check if it is working or not. If It works, It shows that it is not about types, shows it is about empty parameter.

 

If you can share full report jrxml, I can check the parameters are set correctly or not.