Hi Folks,
I have a calculated data item for a column as below:
case
when [StatusName]<>'Withdrawn'
then (current_date - sltapprovaldate)
when [StatusName]='Withdrawn'
then
(null)
when [StatusName]='Joined'
then filleddate - slatapprovaldate
end
the problem is i need to show value 0 when the status is joined
as of now it gives me values for as 22 days, 43 days and null(blank)
i tried to convert the same to cast_varchar (filleddate - sltapprovaldate) and then give as ('0')
but i get something like 0000000022 00:00:000000 for which i have values,
however 0 gets populated when the statusname = joined but rest of values i get as above
i tried all the cast funstions
nothing seems to work fine.
please help...Thanks.
case
when [StatusName]<>'Withdrawn'
then (current_date - sltapprovaldate)
when [StatusName]='Withdrawn'
then
(null)
when [StatusName]='Joined'
then filleddate - slatapprovaldate
end
Did you notice that your case logic will always stop before the 'joined' part. <>'Withdrawn' and = 'Withdrawn' covers each and every possibility, so the 3rd evaluation is never reached!!
Change the logic sequence and use cognos functions by default!!
Thank you very much for the quick response. I didn't understand the use cognos functions by default. Please let me know. Thanks!
You are subtraction dates as if they where numerical figures. Cognos has a nice inbuild function to calculate date differences. Check it out!!
Thank You. Will check the same.