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



Joined: 05 Apr 2007
Posts: 204
Location: Bangalore
Points: 2317

Post Posted: Thu Oct 25, 2007 4:59 am Reply with quote    Back to top    

DataStage® Release: 7x
Job Type: Parallel
OS: Unix
I am using IsValid Function for date validation, as in:

If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS,"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'

Doing this in Stagevariable1.
And mentioned main constarint as Stagevariable='Y'.
whenever i encounter with the invalid data like '20070627 0' or extra values in this field otherthan valid date format,then i am trying to move these into reject link.But its not happening and not moving data into Reject link.
Its working fine with character or alphanumeric data inplace this field.but for '20070627 0' ,its not working.I think my function in stagevariable1 is setting value as "Y" for above kind of data.

Then would be the alternate for this problem.it will work every other invalid data except extra numeric data inplace of date format('YYYYMMDD').
My source datatype is varchar(10) and targget holda as date.In the process,my aim is just to reject the invalid data format into reject link.
Any suggestions..would help me alot.

Thanks in advance.
aquilis

_________________
Aquilis
Maveric
Participant



Joined: 13 Mar 2007
Posts: 388

Points: 2054

Post Posted: Thu Oct 25, 2007 6:43 am Reply with quote    Back to top    

Are you getting any warning in the director saying something like "dropping record due to invalid data"?
Try
If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS[1,8],"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'
using a substring to get only the first 8 characters. Not sure if it is 1,8 or 0,7. Try both.
Rate this response:  
Not yet rated
Aquilis
Participant



Joined: 05 Apr 2007
Posts: 204
Location: Bangalore
Points: 2317

Post Posted: Fri Oct 26, 2007 12:38 am Reply with quote    Back to top    

Maveric,
i am getting error message as :
"ORAcode -01830: date format picture ends before converting entire input string"

its oracle error message,i am using userdefined Sql query to load the adta into oracle DB.In between at transformer i am using that above mentioned function.
If (IsValid('Date', StringToDate(LNK_Read_Input.UPD_TS,"%yyyy%mm%dd"))=0) Then 'N' Else 'Y'

as I am validating the date format in the transformer it should reject and divert rejected data into rejection link.But it parsing the above function and hitting the Database even though the format is invalid.

"dropping record due to invalid data" this error message also i am not getting.

_________________
Aquilis
Rate this response:  
Not yet rated
bikan



Group memberships:
Premium Members

Joined: 08 Jun 2006
Posts: 128

Points: 1241

Post Posted: Fri Oct 26, 2007 12:45 am Reply with quote    Back to top    

Use substring in input column
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: 54072
Location: Sydney, Australia
Points: 293282

Post Posted: Fri Oct 26, 2007 1:32 am Reply with quote    Back to top    

Does your source string date contain any delimiters? Your use of VarChar(10) as a data type suggests that it does. If so, your date format picture must allow for these.

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



Joined: 05 Apr 2007
Posts: 204
Location: Bangalore
Points: 2317

Post Posted: Fri Oct 26, 2007 2:43 am Reply with quote    Back to top    

My source datatype is Varchar2(10).I may get any value either its exact correct date format value or any charcated/flag/dirty value in this column.So only i am validating the values with date fromat('YYYYMMDD').If its doesn't matches with the format so it should reject.Thats why i am using that validation constarint.So i think my validation is going wrong somewhere.could you guide me to validate incoming data with format 'YYYYMMDD'.

Thanks in advance.

_________________
Aquilis
Rate this response:  
Not yet rated
balajisr


since October 2006

Group memberships:
Premium Members

Joined: 28 Jul 2005
Posts: 785

Points: 3770

Post Posted: Fri Oct 26, 2007 3:58 am Reply with quote    Back to top    

I guess that is how StringToDate() works. It reads only characters pertaining to the format string, forms the date and ignores the rest.

Have an extra condition to check the length of the given string to be 8 in addition to IsValid check.
Rate this response:  
Not yet rated
stefanfrost1



Group memberships:
Premium Members

Joined: 03 Sep 2007
Posts: 99
Location: Stockholm, Sweden
Points: 700

Post Posted: Fri Oct 26, 2007 4:23 am Reply with quote    Back to top    

Shouldn't you really do is to check if the string itself is ok, not convert it and then check it. That will fail because the date is not valid... Youre doing it in the wrong order. First check and if it is ok then you can convert it.

You need to change your format on the string ... from your format YYYYMMDD to default YYYY-MM-DD that can easily be done using standard functions.
TmpString = LNK_Read_Input.UPD_TS[1,4]:"-":LNK_Read_Input.UPD_TS[5,2]:"-":LNK_Read_Input.UPD_TS[7,2];

If (IsValid('Date', TmpString)=0) Then 'N' Else 'Y'

_________________
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
Rate this response:  
Not yet rated
stefanfrost1



Group memberships:
Premium Members

Joined: 03 Sep 2007
Posts: 99
Location: Stockholm, Sweden
Points: 700

Post Posted: Fri Oct 26, 2007 4:25 am Reply with quote    Back to top    

Shouldn't you really do is to check if the string itself is ok, not convert it and then check it. That will fail because the date is not valid... Youre doing it in the wrong order. First check and if it is ok then you can convert it.

You need to change your format on the string ... from your format YYYYMMDD to default YYYY-MM-DD that can easily be done using standard functions.

TmpString = LNK_Read_Input.UPD_TS[1,4]:"-":LNK_Read_Input.UPD_TS[5,2]:"-":LNK_Read_Input.UPD_TS[7,2];

If (IsValid('Date', TmpString)=0) Then 'N' Else 'Y'

_________________
-------------------------------------
http://it.toolbox.com/blogs/bi-aj
my blog on delivering business intelligence using agile principles
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