DSXchange

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.

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.

But, I still need to call thsi routine in the derivtion in the Tfm stage right? Hope I am not missign anything here.

Why not IsValid()?

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.

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,

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