DSXchange: DataStage and IBM Websphere Data Integration Forum
View next topic
View previous topic
Add To Favorites
Author Message
sush26283
Participant



Joined: 20 May 2010
Posts: 68

Points: 568

Post Posted: Wed Mar 23, 2011 9:56 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Server
OS: Windows
I need to calculate date, and the requirement is, if the date in column column_X is between the current date and the last 4 years then 'Y' else 'N'
can someone please help me with that, how I can do that in transformer.
Thanks in advance.
soumya5891
Participant



Joined: 07 Mar 2011
Posts: 133

Points: 860

Post Posted: Wed Mar 23, 2011 11:07 am Reply with quote    Back to top    

1. In a transformer or modifier stage define a stage variable say sVar1
seperate out the day and month part from the current date.Now concatinate it with current Year -4 on proper date format.This value is converted into date and stored into sVar1.
2. Now compare between sVar1 and current date with your date and do your processing.


Or

1. In a transfrimer define a stage var sVar1.Four years=365*4=1460(neglecting leap year)
sVar1=DateFromDaysSince(-1460,Current Date).
2. Now compare between sVar1 and current date with your date and do your processing.

Or

1. In a row generator stage generate a row and then in a transfromer stage calculate sVar1 like previous step.And make a dummy col with defaulted 1
2.Make a dummy column as set its value 1 in the incoming data.
3.join 1 and 2 on the basis of dummy col.Then do your processing.

I think in 3 approach the date calculation will be done once,so performence wise this will be better.

_________________
Soumya
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38235
Location: Denver, CO
Points: 195036

Post Posted: Wed Mar 23, 2011 11:17 am Reply with quote    Back to top    

Server job.

_________________
-craig

Mama always told me not to look into the eyes of the sun - but mama, that's where the fun is!
Rate this response:  
Not yet rated
sush26283
Participant



Joined: 20 May 2010
Posts: 68

Points: 568

Post Posted: Wed Mar 23, 2011 12:18 pm Reply with quote    Back to top    

chulett wrote:
Server job.

yes server job
Rate this response:  
Not yet rated
chulett

Premium Poster


since January 2006

Group memberships:
Premium Members, Inner Circle, Server to Parallel Transition Group

Joined: 12 Nov 2002
Posts: 38235
Location: Denver, CO
Points: 195036

Post Posted: Wed Mar 23, 2011 12:42 pm Reply with quote    Back to top    

I know, it wasn't a question... I posted it because the answer you got was specific to PX jobs.

_________________
-craig

Mama always told me not to look into the eyes of the sun - but mama, that's where the fun is!
Rate this response:  
Not yet rated
sush26283
Participant



Joined: 20 May 2010
Posts: 68

Points: 568

Post Posted: Wed Mar 23, 2011 12:48 pm Reply with quote    Back to top    

chulett wrote:
I know, it wasn't a question... I posted it because the answer you got was specific to PX jobs.


i did figure that out, so what would you suggest
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51340
Location: Canberra, Australia
Points: 278770

Post Posted: Wed Mar 23, 2011 1:33 pm Reply with quote    Back to top    

Use a database stage (eg ODBC stage) to service the reference link, and use user-defined SQL that specifies the BETWEEN condition. You can generate the two dates using SQL functions.

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
sush26283
Participant



Joined: 20 May 2010
Posts: 68

Points: 568

Post Posted: Wed Mar 23, 2011 1:38 pm Reply with quote    Back to top    

ray.wurlod wrote:
Use a database stage (eg ODBC stage) to service the reference link, and use user-defined SQL that specifies the BETWEEN condition. You can generate the two dates using SQL functions. ...


but wont it be better performance wise if we do it in transformer itself..
Rate this response:  
Not yet rated
ray.wurlod

Premium Poster
Participant

Group memberships:
Premium Members, Inner Circle, Australia Usergroup, Server to Parallel Transition Group

Joined: 23 Oct 2002
Posts: 51340
Location: Canberra, Australia
Points: 278770

Post Posted: Wed Mar 23, 2011 1:53 pm Reply with quote    Back to top    

Depends mainly on where the database is.

Within the transformer stage it's a simple If..Then..Else test with a binary condition (date >= fouryearsago And date <= currentdate)

_________________
RXP Services Ltd
Melbourne | Canberra | Sydney | Hong Kong
currently hiring: Sydney and Melbourne
Rate this response:  
Not yet rated
Display posts from previous:       

Add To Favorites
View next topic
View previous topic
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum



Powered by phpBB © 2001, 2002 phpBB Group
Theme & Graphics by Daz :: Portal by Smartor
All times are GMT - 6 Hours