DSXchange

I need to add a month to date and do some checks in transformer stage.My source is from flat file.I can write routine to call Oracle and use Add_Months function in oracle but it opens up and closes oracle session for each row when I call routine in transformer stage.Any suggestions will be appreciated.

Instead of making a call to oracle in a routine, just custom build the routine to add 30 days or 31 days to the iconv of the date. Search this forum for many examples.
Once that is done, you can do further manipulation in the transformer itself.

[quote="narsingrp"]I need to add a month to date and do some checks in transformer stage.My source is from flat file.I can write routine to call Oracle and use Add_Months function in oracle but it opens up and closes oracle session for each row when I call routine in transformer stage.Any suggestions will be appreciated.[/quote]

Thanks for reply.I will search for similar routines in forum.
The problem is when my date is 31-JAN-2006,if I add 30 or 31 days,this won't work.And no.of days in FEB month in leap years will be 29 days etc.

yea, you will have to code it. Your routine should be smart enough to handle leap years, and know when to add 30 days and when to add 31 days. Its not easy, i agree, but its dooable.
OR if you want the easy way out, load your flat file in a temp table and then use oracle's function of add_months in the user defined sql.

[quote="DSguru2B"]yea, you will have to code it. Your routine should be smart enough to handle leap years, and know when to add 30 days and when to add 31 days. Its not easy, i agree, but its dooable.
OR if you want the easy way out, load your flat file in a temp table and then use oracle's function of add_months in the user defined sql.[/quote]

Yaa.It is dooable but I think going for temp table is good idea.

There's a link on this page to some routines including one that adds months to a date.