Using Coalesce with Intervals

[locus]

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

[bdbits]

What database is this? Can you post the expression?

[locus]

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

[bdbits]

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