Date and Number validations

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
vskr72
Premium Member
Premium Member
Posts: 128
Joined: Wed Apr 28, 2004 9:36 pm

Date and Number validations

Post by vskr72 »

I have a job that loads data from a SEQ file to Ora table. While loading I need to check the following conditions for Date and Number columns:

1. If its a valid date, then insert the value else set it to NULL
2. If its a valid number, then insert the value else set it to NULL.

Implementing them is not an issue. I have used 'IF..THEN..ELSE' statements. Works fine. I am using a Transformer for this. But the only issue is there are too many Date or Number columns. Writing these statements for each of them is very painful and it takes a lot of time. There are many jobs also. Is there any other way to handle this outside Transformer or using any Stage variables(???). Thank you for your inputs.
shamshad
Premium Member
Premium Member
Posts: 147
Joined: Wed Aug 25, 2004 1:39 pm
Location: Detroit,MI

Post by shamshad »

Can there be 2 routines, one for DATE CHECK and one for NUMBER CHECK. Just pass the column value to the routine and get either a value or NULL as OUTPUT. Then insert into table.
Datawarehouse Consultant
vskr72
Premium Member
Premium Member
Posts: 128
Joined: Wed Apr 28, 2004 9:36 pm

Post by vskr72 »

But, I still need to call thsi routine in the derivtion in the Tfm stage right? Hope I am not missign anything here.
pandeesh
Premium Member
Premium Member
Posts: 1399
Joined: Sun Oct 24, 2010 5:15 am
Location: CHENNAI, TAMIL NADU

Post by pandeesh »

Why not IsValid()?
pandeeswaran
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

I would assume that is what they are doing. And I assume the goal here would be to reduce all that if-then-else activity down to something more manageable. As noted, a parallel routine could cut that down but nothing will remove the fact that you'll need to do something to each and every field you want to validate. Yes, it will be repetitive and boring, but that's part of the game.

So you could have a 'ValidateDate' routine that you simply wrap around any date field, the result of which is either the incoming date if it was valid or a null if it wasn't. Same thing for validating your number fields. Of course, inside the routine it is still doing that same if-then-else check.
-craig

"You can never have too many knives" -- Logan Nine Fingers
jwiles
Premium Member
Premium Member
Posts: 1274
Joined: Sun Nov 14, 2004 8:50 pm
Contact:

Post by jwiles »

Writing this logic within a transformer is going to take some measure of repetition, whether that is the if-then-else logic you are currently using or the parallel routines mentioned in the thread. This is the nature of the transformer interface and logic...no dynamic access to the columns passing in or out of the stage.

The parallel routines will likely be your best solution unless you want to tackle something more dynamic such as a Java Transformer or custom operator (C++). They will be somewhat more difficult to implement than a single-function parallel routine.

Regards,
- james wiles


All generalizations are false, including this one - Mark Twain.
vskr72
Premium Member
Premium Member
Posts: 128
Joined: Wed Apr 28, 2004 9:36 pm

Post by vskr72 »

Thank you for all your inputs. So, I guess I will have to manage this cumbersome process. I will at least try to develop a routine to reduce the lengthy if-then-else statements
Post Reply