Date and Number validations

vskr72 · Posted: Wed Dec 21, 2011 5:16 pm

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 · Posted: Wed Dec 21, 2011 6:56 pm

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.

vskr72 · Posted: Wed Dec 21, 2011 8:07 pm

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

pandeesh · Posted: Wed Dec 21, 2011 8:11 pm

Why not IsValid()?

chulett · Posted: Wed Dec 21, 2011 8:50 pm

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 ...

What's this?

jwiles · Posted: Wed Dec 21, 2011 9:21 pm

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,

vskr72 · Posted: Wed Dec 21, 2011 11:51 pm

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