Transformer Stage Vs Filter Stage

vigneshra · Post by **vigneshra** » Wed Sep 29, 2004 7:39 am

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.

dsxdev · Post by **dsxdev** » Wed Sep 29, 2004 8:04 am

For small operations like this Filter would be a better option.
Transformer is more robust than a Filter Stage. :D

dsxdev · Post by **dsxdev** » Wed Sep 29, 2004 8:08 am

Hi,
if you already have a transformer doing the transformations
added to those you need to split the records based on the range of a column value then better you implement this in transformer using Link Constraints and Stage Variables.

vigneshra · Post by **vigneshra** » Wed Sep 29, 2004 11:45 pm

Infact no other transformations involved. It is a straight pull from source table to 7 target tables based on the date range. So can I assume that filter stage gives a better performance.

Vignesh.

vigneshra · Post by **vigneshra** » Thu Sep 30, 2004 12:57 am

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.

vigneshra · Post by **vigneshra** » Thu Sep 30, 2004 1:07 am

I got it folks !! I put the syntax here. May be it will be of use to someone.

TRANSACTION_DATE BETWEEN '#TABLE1_START_DATE#' AND '#TABLE1_END_DATE#'

Thanks !!
Vignesh.

mandyli · Post by **mandyli** » Thu Sep 30, 2004 2:42 am

Hi Vignesh,

The ans is simple but great .

vigneshra · Post by **vigneshra** » Fri Nov 05, 2004 12:06 am

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