Clarity

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:

ERRORError 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).  

The question(s):

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.

Thanks,

  - Mark 

Would you like to share that part of the NSQL as wekk.

Hi ; you have me somewhat confused ; you talk about 'SQL Management Studio' which implies you are on a SQL Server database, yet you then use NVL which is an Oracle function?  Which database are you on?

Some comments that might help you though...

"DATEDIFF" is a SQL Server function, Oracle has different functions ; e.g. "MONTHS_BETWEEN" will just give you a decimal number for the difference between two dates.

I think you are getting errors in the application just because you have "assumed" a standard date format for your date - you have used the default Oracle date format (which is why it "works" in your SQL tool but the application will not necessarily use the same date format. If your logic really needs to cast a string to a date then you should always include the format in that cast to get around this. In Oracle you would just need to use to_date with an explicit format mask ( e.g. to_date('01-JAN-89','DD-MON-YY') ) in SQL Server you use CONVERT with format type 106 (I think)

Not sure why you say there is no ISNULL() function - in SQL Server there is, in Oracle it is just NVL(). If you want to make NSQL code agnostic to the database use @NVL@ and that takes care of either.

You can do the "date difference in months" in a number of ways depending on your required logic - the way you have it at the moment is OK, but as you realise it is the number of days between two dates divided by 30.

As mentioned above there is an Oracle function to return a decimal number for the number of months between two dates though ; unsurprisingly it is the MONTHS_BETWEEN function - could just use that and round the answer as you need to?   

But you could want to say the difference between the "month of date 1" and the "month of date 2"

(so convert each date to a month value using TRUNC (in Oracle) ; trunc ( mydate , 'MONTH') will return the date of the first day in a month, then use MONTHS_BETWEEN on the results).

 Look here for some comparison of DATEDIFF and MONTHS_BETWEEN ; MONTHS_BETWEEN Function - Oracle to SQL Server Migration - SQLines Open Source Tools ( since I still am not sure what DB you are on!  ) 

Thanks!  You are correct, I'm using Oracle SQL... previous job I was using SQL Mgt Studio, and I wrote the question up after poking at the SQL for waaay too long.  And switching between MySQL and Oracle SQL has provided some challenges (e.g. ISNULL() vs. NVL).  

I'll take a look at MONTHS_BETWEEN, that should help trim down the SQL a bit.  

I'll also look at casting my dates (fingers crossed!).

I'll have to look at @NVL@ more.  I was using NVL(Field,<replacement_value>), I've not used @NVL@ before, and therefore am unfamiliar.  

Thanks so much for the quick and detailed reply!  You've no idea how much time I've spent trying to get these calculations to work.

If @NVL@ doesn't resolve the issue, and given you are receiving an Internal Server Error, you may want to check the app logs for a corresponding error message.  That should shed more light on the issue.

The "error" is going to be the date format ; (seen it before) - the 'nvl' in the SQL was fine (the query would not save it it wasn't).   to_date('01-JAN-89','DD-MON-YY') ) would "fix "the error (you don't need the to_date for the MONTHS_BETWEEN though as both parameters to that will already be dates (rather than strings) - well they should be dates anyway!

Just to clear about @NVL@ - its a NSQL construct that the application will substitute nvl for in Oracle databases and isnull for in SQL Server ones. Similar constructs are @+@ for || or + in string concatenations. In theory this helps is write NSQL that can be used in either Oracle or SQL databases.... unfortunately for anything a bit more complicated (like date formats) there is no NSQL 'shortcut' and you'd just have to code it differently for Oracle or for SQL Server.

Just wanted to report, success all around (casting dates & months between)!  Thanks again for the help!