Coalesce

[chrisssyp82]

Crystal 2008

I want to Coalesce a formula like in SQL but I am having trouble with the syntax. Below is how i would think it could be written but i'm getting errors:

IF ISNULL(COALESCE({tblPolicy.QuoteDate}),{tblPolicy.CreationDate}, {tblPolicy.CoverFrom}),’’) <> ‘’ AND {tblPolicy.Status} in ["OnRisk", "HeldCover", "NTU", "Declined", "Cancelled", "Lapsed", "Quote"]
THEN COALESCE ({tblPolicy.QuoteDate}),{tblPolicy.CreationDate}, {tblPolicy.CoverFrom})


Can someone point me in the right direction or help me re-write it?

Many thanks

 

[lbass]

Assuming these dates are in chronological order and are of date datatype, then I think you could replace each field with a formula like this:

//{@QuoteDate}:
if isnull(tblPolicy.QuoteDate}) or
{tblPolicy.QuoteDate} = date(0,0,0) then
date(9999,9,9) else
{tblPolicy.QuoteDate}

Then you could rewrite your formula like this:

if minimum([{@QuoteDate},{@CreationDate},{@CoverFrom}]) <> date(9999,9,9) and
{tblPolicy.Status} in ["OnRisk", "HeldCover", "NTU", "Declined", "Cancelled", "Lapsed", "Quote"] then
minimum([{@QuoteDate},{@CreationDate},{@CoverFrom}])

-LB