Using $V variables in query



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:


count(*) 'Total Alarms',
from alarminfo
 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:


    $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()




    $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 to getStart() or getEnd() of a given date

Day = new"DAY-1").toDateRange()
Then  StartDay = $P{Day}.getStart()         (java.util.Date)
           EndDay = $P{Day}.getEnd() 

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