Trim in FILTER stage

[durgaps]

Hi,

I am trying to filter around 200 records using the FILTER stage on the predicate FundCode = Fund. These 2 are input columns which are VarChar(2) & VarChar(6) respectively. This is not working.

I also tried with Trim(FundCode) = Trim(Fund) again with no positive results. I guess TRIM cannot be used in the FILTER stage. Is there any other function in place of TRIM or any other way we can do it?

[Daddy Doma]

To create two columns with matching lengths, add a Transformer stage before the Filter and use this to create new column(s) with matching length.

Or, use a Transformer stage to assess if each record should pass. Assess Fund = FundCode as a Stage Variable and then add a Constraint to your output to only allow the passes, i.e.

Stage Variable
vEqualFund: IF Fund = FundCode THEN 1 ELSE 0

Constraint
vEqualFund = 1

[durgaps]

Hi,

I am trying to filter around 200 records using the FILTER stage on the predicate FundCode = Fund. These 2 are input columns which are VarChar(2) & VarChar(6) respectively. This is not working.

I also tried with Trim(FundCode) = Trim(Fund) again with no positive results. I guess TRIM cannot be used in the FILTER stage. Is there any other function in place of TRIM or any other way we can do it?

Hi,

Thanks for the reply. I require a solution using FILTER stage, usage of TRANSFORMER is not planned/proposed. Please help.

[ray.wurlod]

One stage, one task. Fundamental tenet of parallel job design.

The Filter stage does not trim.

You can trim using either a Modify stage or a Transformer stage.

No other stage has a Trim() capability, unless you write your own custom stage.

My advice is to use a Modify stage upstream of the Filter stage to apply the required trim or substring operation.

[kumar_s]

But you can try with the frist 2 character of the Varcahr(6) field to compare with.

[alphac]

The "where clause" of Filter Stage does not allow "trim function" in it.
Extra stage to handle "trim" is needed.

On the other hand, you should pay attention to the where clause you set.
In your case, set
a = b is different from b = a
for a is varchar(2) and b is varchar(6).

if you set a = b, engine will truncate b to varchar(2) first and then compare with a.

if you set b = a, engine will not perform type conversion.

e.g. where clause is "a = b"
if a = "no", b = "no" then a = b hits.
if a = "no", b = "no " then a = b also hits.

where clause is "b = a"
if a = "no", b = "no" then b = a hits.
if a = "no", b = "no " then b = a miss.

==> result is not what you expected. so you see an extra stage is needed. right ?

[kumar_s]

if you set a = b, engine will truncate b to varchar(2) first and then compare with a.

if you set b = a, engine will not perform type conversion.

.....

This is a nice study over filter stage on your first post alphac. :D

[durgaps]

Hi all,

Thanks for the replies/info.

Regards