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



Joined: 11 Mar 2012
Posts: 19
Location: Detroit
Points: 282

Post Posted: Thu Jul 12, 2018 11:56 am Reply with quote Back to top

DataStage® Release: 11x
Job Type: Parallel
OS: Unix
Hello, I have two fields in the database where we are storing First Name and Last Name with varchar(60), the job is running fine with normal characters. But recently, we started receiving junk data and asked to remove these records from further processing. Can you guys please share if there is anyway to handle this?

Code:
Андиян Аив
ниамин Хбов

I know, these might be valid in someother NLS, but we are asked to error out.

Thanks in advance.

_________________
DataStage 11.3 on AIX 7.1 with DB2.
qt_ky



Group memberships:
Premium Members

Joined: 03 Aug 2011
Posts: 2788
Location: USA
Points: 21026

Post Posted: Thu Jul 12, 2018 12:55 pm Reply with quote Back to top

One approach is to use a string function first to strip all white space then use another string function like AlNum() to checks whether the given string contains only alphanumeric characters or not.

_________________
Choose a job you love, and you will never have to work a day in your life. - Confucius
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: 54248
Location: Sydney, Australia
Points: 294235

Post Posted: Thu Jul 12, 2018 1:38 pm Reply with quote Back to top

Step 1: sort out your data provider(s). Step 2: use the "double Convert()" technique to preserve only wanted characters. Code: Convert(inLink.TheString, Convert(inLink.theString, ...

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



Group memberships:
Premium Members

Joined: 21 Mar 2016
Posts: 331

Points: 3352

Post Posted: Fri Jul 13, 2018 2:15 pm Reply with quote Back to top

to just remove the records entirely you can do it with a single convert, or possibly even more efficiently with 'alpha' if that fits the data need? A single convert, you convert all the valid chars to nothing and then check the length of the remainder.

double convert fixes the data, which is a different thing from "remove these records from further processing"
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