Hi Folks,
I have a report with multiple columns each displaying an interval calculated between a date_time field, and a CHAR field that has been cast into a timestamp.
The formats are the same just the data types differ.
The interval result comes back fine.
The CHAR field was brought in outside of my control (Originally 2 date_times to work with)
As not all of the columns are always populated, I originally used coalesce to ensure I returned a value for the group (Instead of a null)
Now when trying coalesce I get the following error:
The operation "condexp" is invalid for the following combination of data types: "interval2" and "integer
Attempts to have the new field's data type changed to date_time (Which it essentially is) have failed.
How can I achieve a result using coalesce (Or any other method)?
Cheers,
Locus
What database is this? Can you post the expression?
Interval column expression (SQL Server):
CASE WHEN ([PO APPROVAL MILESTONES PREPROD].[EVENT_D].[EVENT_CODE] = 'SR191') THEN ([TRIGGER_HOURS] - [ORDER_DATE] ) ELSE NULL END
EVENT_CODE differs for each column but the interval calculation is the same.
ORDER_DATE is the date_time & TRIGGER_HOURS is the cast from CHAR
Returning a result in the format : 0 00:28
Coalesce is: COALESCE([EXPRESSION 1],0)
Columns summary is COALESCE([EXPRESSION 1],0)+COALESCE([EXPRESSION 2],0)+COALESCE([EXPRESSION 3],0)....etc
I think the error message is saying "hey that calculation returns an interval, but your COALESCE is trying to substitute an integer value of 0". As far as I know SQL Server does not support interval data types, so I assume the interval calculation is being done by Cognos, which makes me wonder what SQL Server is getting in the generated query. I am a bit hazy on what SQL Server is processing versus Cognos, and where the cast is happening.
Maybe try something like this, which eliminates the COALESCE and uses a Cognos function int2DTinterval to cast a 0 to an interval, in this case minutes.
CASE WHEN ([PO APPROVAL MILESTONES PREPROD].[EVENT_D].[EVENT_CODE] = 'SR191') THEN
CASE WHEN [TRIGGER_HOURS] IS NULL OR ORDER_DATE IS NULL THEN int2DTinterval(0,"m")
ELSE ([TRIGGER_HOURS] - [ORDER_DATE] )
END
ELSE int2DTinterval(0,"m")
END