Routine Problem

shiva459 · Post by **shiva459** » Thu Jun 19, 2003 6:50 am

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

kduke · Post by **kduke** » Thu Jun 19, 2003 8:03 am

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

shiva459 · Post by **shiva459** » Thu Jun 19, 2003 9:50 am

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

tonystark622 · Post by **tonystark622** » Thu Jun 19, 2003 11:13 am

Shivakumar,

I've just gone through a problem where the job didn't appear to be processing any rows. It turned out to be "slow lookups" because the fields that I was joining weren't indexed. Painfully slow. So, indexes do make a difference.

Tony

kduke · Post by **kduke** » Thu Jun 19, 2003 1:48 pm

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