Jaspersoft Community 'Read-Only' as of July 8, 2022
Transition to New TIBCO Community Just Weeks Away
You can still search, review wiki content, and review discussions in read-only mode. Please email email@example.com with questions or issues requiring TIBCO review or response.
I'm having a problem getting a relative date to work when trying to create a derived table. We are utilizing some of the monitoring data to see when users have accessed reports. To make sure the usage is somewhat current, I wanted to see any user that hasn't accessed a report in say, two months time. I came up with this derived table becasue the built in join functions wouldn't really allow me to filter out the positives (and leave just the null results). This should give me anyone who has not accessed a report ever, or has not accessed a report in a long time. The compiler gets mad when I add the date_sub function. I haven't found anything in the DomEL Syntax docs to suggest a way to do relative dating.
SELECT jiu.id AS user_id, jiu.fullname AS fullname, jiu.username AS username, jia.event_date, jia.id AS access_id FROM jiuser jiu LEFT JOIN jiaccessevent jia ON jia.user_id = jiu.id WHERE (jia.id IS NULL OR jia.event_date <= DATE_SUB(NOW() - INTERVAL 60 DAY))
I appreciate any thoughts or suggestions anyone has.