Jump to content
Changes to the Jaspersoft community edition download ×

Please Change the syntax of sql query that will be supported by jaspersoft server


girish.gandhi

Recommended Posts

Hi Team ,

I want to publish a report using jaspersoft studio . As i am new to jaspersoft , I dont have much knowledge about its syntax for writing the sql query that will contain the parameters. I have the following query written and is currently working on sql management studio . The Query filters the records between given specified date and time range based on the condtions. I want the below mentioned query to get its syntax changed that will work sucessfully and will be supported in jaspersoft studio to publish report.

................................................................................................................................................................................................................................................................................................................................................................................

DECLARE @Start_Date DATE='2018-08-14'
DECLARE @S_TIME TIME='09:00:0.000'
DECLARE @E_TIME TIME='17:00:0.000'
DECLARE @E_DATE DATE='2018-10-24'
DECLARE @DateRange Int=1
DECLARE @Business_Hours INT=1
if(@DateRange=1 and @Business_Hours=1)
select *from sampletable where cast(sampletime as date) between @Start_Date and @E_Date and
cast(sampletime as time) between @S_TIME and @E_TIME
else if(@DateRange=0 and @Business_Hours=1)
select *from sampletable where cast(sampletime as time) between @S_TIME and @E_TIME
else if(@DateRange=1 and @Business_Hours=0)
select *from sampletable where cast(sampletime as date) between @Start_Date and @E_Date
else if(@DateRange=0 and @Business_Hours=0)
select *from sampletable
...........................................................................................................................................................................................................................................
Explanation -

Query prints records from the table using if else condition . Whatever condition evaluates true query will filter records between give date period and given time period.
 Query uses @DateRange Int=1 , @Business_Hours INT=1 as a flag variable .

sampletime is the DATETIME type field in my table sampletable

DECLARE @Start_Date DATE='2018-08-14'
DECLARE @S_TIME TIME='09:00:0.000'
DECLARE @E_TIME TIME='17:00:0.000'
DECLARE @E_DATE DATE='2018-10-24'
DECLARE @DateRange Int=1
DECLARE @Business_Hours INT=1

These Variables values, I need to take from parameters and using input controls created in the report. If we change the values of @DateRange , @Business_Hours variables query result will get changed.

Please help .

Thanks in Advance

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

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...