for example :
declare @bgdt datetime,@eddt datetime
set @bgdt=@begindate
set @eddt=@enddate
how can i write the sql in jasperreport dataset?
1 Answer:
Posted on June 3, 2023 at 5:02am
Move your code into a stored procedure then use a data adapter with plsql language. That plsql language is not just for Oracle but it works on sql server too. When you change the SQL language a new default parameter is created as $P{ORACLE_REF_CURSOR} which holds the result of the procedure.
Here is an example of how to call a stored procedure:
{ call SP_RPT_PDM2_101_004_2023($P{PS_M_ACAD_YR},$P{PS_M_COLL_NAME},$P{PS_M_SUBM_NUM},$P{PS_M_ORG_ID},$P{ORACLE_REF_CURSOR}) }
Thank you for posting to the Jaspersoft Community. Our team of experts has read your question and we are working to get you an answer as quickly as we can. If you have a Jaspersoft Professional Subscription plan, please visit https://support.tibco.com/s/ for direct access to our technical support teams offering guaranteed response times.
Minor note - if you're hosting these reports on jasper server, you'll need to set the 'language' attribute on the <queryString> element to 'plsql' to support the 'call' keyword, so <queryString language="plsql"; alternatively you can change the sql validator on the properties file on jasper. If the 'langauge="plsql" approach doesn't work, look into the validator.