Query for input control wont work in Jaspersoft, but works in My SQL Server

0

I have an input control that will need to be populated by a query. When I wrote the code in My SQL Server, it works perfectly as planned. However, when I put it in the the input control, the drop down is blank. Are cases not allowed in the input control queries? Is there a work around?

here is the seemingly simple sql code:

 

SELECT a.PRSTART as date,
  case when(a.prstart <= getdate() - 7 and a.prfinish - 1  >= getdate() - 7) then 'Previous Period'
            when (a.prstart <= getdate() + 7 and a.prfinish - 1  >= getdate() + 7) then 'Next Period'
            else 'Current Period' end as period
        FROM   PRTIMEPERIOD a
        WHERE 
        (a.prstart <= getdate() and a.prfinish - 1  >= getdate())
        or
        (a.prstart <= getdate() - 7 and a.prfinish - 1  >= getdate() - 7)
        or
        (a.prstart <= getdate() + 7 and a.prfinish - 1  >= getdate() + 7)

as you can see, I need to return "Previous", "next", or "current time period" dynamically along with the date calculated. for the input control, previous, next, and current would be the visible field, while the date would be used as the value column.

thanks!

TheMatthew's picture
Joined: May 16 2016 - 1:26pm
Last seen: 1 year 10 months ago

0 Answers:

No answers yet
Feedback
randomness