Hello CA PPM Community,
I'm looking for some assistance with a SQL query I'm writing for a portlet. I'm trying to get the difference (in months) between two custom date fields (Contract Signature Date [CSD] and Go Live Date [GLD]). The DateDiff function doesn't seem to work; I can just do ABS(GLD-CSD)/30 to get a close estimate (where 30 is the avg number of days in a month). As expected, I ran into issues with NULL values for either of the dates. And I've tried several different variations to get around it, with no success. The SQL code compiles, and I can run the script (in SQL Management Studio), but keep getting this once the query is used in the portlet:
Error 500 - Internal Server Error. The server could not retrieve the document due to server-configuration or other technical problems. Contact your site administrator.
Here's one version:
CASE WHEN NVL(go_live_dt,'01-JAN-89') = '01-JAN-89' THEN 0
WHEN NVL(contract_sign_dt,'01-JAN-89') = '01-JAN-89' THEN 0
ELSE round(abs(go_live_dt - contract_sign_dt)/30,0) END as contract_to_gl
I hate that there's no ISNULL() function... hence the NVL with made-up-date for comparison. As mentioned, when run in SQL Management Studio, I receive the correct values (0 when either date is NULL or a # when both are populated).
First, why does the SQL script compile in CA PPM, and work in SQL Management Studio, but the portlet fails? (inquiring minds want to know... as the case statement shouldn't produce NULL Values).
AND second, is there a better formula/approach to accomplish the same end? I can probably create a calculated field on the Project Object, but I'd prefer to tackle this in the query.