Hi
In our project, we have a requirement like we need to forward records from one table to 7 other tables based on the value of a date column. If date falls in range1 then it should go to table 1 or if it falls in range2 then it should go to table 2 and so on. Which stage performs well in this case TRANSFORMER or FILTER ?? The data we are handling is in the range nearing billion. All your suggestions welcome.
Vignesh.
Transformer Stage Vs Filter Stage
Moderators: chulett, rschirm, roy
I tried using filter stage for doing this but ended up in the job abortion due to incorrect syntax in where clause. I explain you li'l bit more. Here in this job, we are passing the date parameter values to the job through a unix script. One of the columns input to the filter stage is a date column which is compared against these parameters. The input column is TRANSACTION_DATE and it is compared against the parameters like (TABLE1_START_DATE and TABLE1_END_DATE), (TABLE2_START_DATE and TABLE2_END_DATE) and so on.
Can I know the exact syntax for putting in where clause..
I put like
TRANSACTION_DATE BETWEEN #TABLE1_START_DATE# AND #TABLE1_END_DATE#
TRANSACTION_DATE BETWEEN #TABLE2_START_DATE# AND #TABLE2_END_DATE#
and so on.........
But the job got aborted. Please throw me some light on this.
Can I know the exact syntax for putting in where clause..
I put like
TRANSACTION_DATE BETWEEN #TABLE1_START_DATE# AND #TABLE1_END_DATE#
TRANSACTION_DATE BETWEEN #TABLE2_START_DATE# AND #TABLE2_END_DATE#
and so on.........
But the job got aborted. Please throw me some light on this.
Still the question remains. If there is a situation where either transformer or filter can be used, which one to choose ?? I came across a document stating that using transformer is expensive. If so, in what way is it expensive ?? Ray, Craig, Burney, Bland, Duke and other DataStage Gurus, can you give a pleasing answer to this question ??