Hi
I have a if statement which calculates the claim age for the products.I get the sale date from a lookup.Once i start the job,the monitor shows status as running,but doesn't process any rows.I guess it is problem with statement.I am posting the routine, can any one correct me?
IF IsNull(SALE_DT)
THEN
Ans=0
RETURN(Ans)
END
IF SALE_DT="1900-12-31"
THEN
Ans=@NULL
RETURN(Ans)
END
Ans=(Iconv(CLAIM_DT,"D-YMD")-Iconv(SALE_DT,"D-YMD"))/31
IF MOD(Ans,1)=0
THEN
RETURN(Ans)
END
ELSE
Ans=Int(Ans)+1
RETURN(Ans)
END
Regards
Shiva
Shivakumar
Routine Problem
Moderators: chulett, rschirm, roy
Shiva
I am not sure what you are trying to do. If you mod something by 1 then it is always zero. If CLAIM_DT and SALE_DT are not arguments then that might be a problem. If you are trying to calculate the difference in months then their is a lot easier way. Here is my best guess:
SALE_DT=Arg1
CLAIM_DT=Arg2
Ans=(Iconv(CLAIM_DT,"D-YMD")-Iconv(SALE_DT,"D-YMD"))/31
begin case
case IsNull(SALE_DT)
Ans=0
case SALE_DT="1900-12-31"
Ans=@NULL
case Ans=Int(Ans)
null
case @TRUE
Ans=Int(Ans)+1
end case
return(Ans)
Thanks Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I am not sure what you are trying to do. If you mod something by 1 then it is always zero. If CLAIM_DT and SALE_DT are not arguments then that might be a problem. If you are trying to calculate the difference in months then their is a lot easier way. Here is my best guess:
SALE_DT=Arg1
CLAIM_DT=Arg2
Ans=(Iconv(CLAIM_DT,"D-YMD")-Iconv(SALE_DT,"D-YMD"))/31
begin case
case IsNull(SALE_DT)
Ans=0
case SALE_DT="1900-12-31"
Ans=@NULL
case Ans=Int(Ans)
null
case @TRUE
Ans=Int(Ans)+1
end case
return(Ans)
Thanks Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
Hi Kim
Thanks for your reply.I was trying to find the difference in months.If i get difference of 1.1 then it should go as 2 months into the ClaimAge.Basically i was trying to round to the next higher value.And the Mod was to check for Integer.
But is it the routine giving problem or the lookup table?I do not have any indexes on the lookup table.But lookups in other jobs are giving fine performance without indexes.I am not able to figure out the problem in this job.
Thanks
Shiva
Shivakumar
Thanks for your reply.I was trying to find the difference in months.If i get difference of 1.1 then it should go as 2 months into the ClaimAge.Basically i was trying to round to the next higher value.And the Mod was to check for Integer.
But is it the routine giving problem or the lookup table?I do not have any indexes on the lookup table.But lookups in other jobs are giving fine performance without indexes.I am not able to figure out the problem in this job.
Thanks
Shiva
Shivakumar
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
Shiva
I think what I gave you is correct if 31 days defines a month.
Year1 = field(CLAIM_DT,"-", 1)
Year2 = field(SALE_DT,"-", 1)
Month1 = field(CLAIM_DT,"-", 2)
Month2 = field(SALE_DT,"-", 2)
Day1 = field(CLAIM_DT,"-", 3)
Day2 = field(SALE_DT,"-", 3)
Months = (Year1 - Year2)*12
Months += Month1 - Month2
if Day2Day1 then Months -= 1
You need to trap nulls but this maybe more accurate.
Thanks Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com
I think what I gave you is correct if 31 days defines a month.
Year1 = field(CLAIM_DT,"-", 1)
Year2 = field(SALE_DT,"-", 1)
Month1 = field(CLAIM_DT,"-", 2)
Month2 = field(SALE_DT,"-", 2)
Day1 = field(CLAIM_DT,"-", 3)
Day2 = field(SALE_DT,"-", 3)
Months = (Year1 - Year2)*12
Months += Month1 - Month2
if Day2Day1 then Months -= 1
You need to trap nulls but this maybe more accurate.
Thanks Kim.
Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com