Using $V variables in query

Hello

 

I want to use a $V variable in my query. As i have already found out this does not work. So I created a parameter that refers to the variable but unfortunately my output is null. 

This is how i'am trying to create my query:

I have 2 variables: 1, firstDayOfLastMonth and 2, lastDayOfLastMonth

And I have 2 parameters: 1, start_date and 2, end_date. In the parameters start_date the default value expression is $V{firstDayOfLastMonth} and in end_date it is $V{lastDayOfLastMonth}.

My query is:

 

SELECT
count(*) 'Total Alarms',
from alarminfo
where
 alarminfo.set_time>=$P{start_date} AND alarminfo.set_time<$P{end_date}

 

There are no compiling errors, but somehow everything stays NULL 

 

Does anyone have an idea what I am doing wrong? 

CaPsLoCkEr's picture
Joined: Oct 9 2017 - 5:35am
Last seen: 1 year 11 months ago

Hello CaPsLoCkEr,  please share the logic/expressions used for creating 2 variables: 1, firstDayOfLastMonth and 2, lastDayOfLastMonth. 

santhosh.devadurga - 4 years 6 months ago

Hello Santhosh:

firstdayoflastmonth: 

(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, 1)
)
? null : $P{cal}.getTime()

 

 

lastdayoflastmonth:

(
    $P{cal}.set($P{runDate}.getYear()+1900, $P{runDate}.getMonth(), $P{runDate}.getDate()) ||
    $P{cal}.add(Calendar.MONTH, -1) ||
    $P{cal}.set(Calendar.DAY_OF_MONTH, $P{cal}.getActualMaximum(Calendar.DAY_OF_MONTH))
)
? null : $P{cal}.getTime()

CaPsLoCkEr - 4 years 6 months ago

1 Answer:

Why can't you just use the logic in the parameter ? Should work. 

Also you could try and use net.sf.jasperreports.types.date.DateRange to getStart() or getEnd() of a given date

Example: 
Day = new net.sf.jasperreports.types.date.DateRangeBuilder("DAY-1").toDateRange()
Then  StartDay = $P{Day}.getStart()         (java.util.Date)
           EndDay = $P{Day}.getEnd() 

joseng62's picture
4029
Joined: Dec 5 2014 - 2:43am
Last seen: 7 months 2 weeks ago
Feedback
randomness