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.
Date and Number validations
Moderators: chulett, rschirm, roy
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.
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
"You can never have too many knives" -- Logan Nine Fingers
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,
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.
All generalizations are false, including this one - Mark Twain.