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?
Trim in FILTER stage
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 74
- Joined: Sat Jul 08, 2006 4:09 am
- Location: Melbourne, Australia
- Contact:
Trim in FILTER stage
Durga Prasad
-
- Premium Member
- Posts: 62
- Joined: Tue Jun 14, 2005 7:17 pm
- Location: Australia
- Contact:
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
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
-
- Participant
- Posts: 74
- Joined: Sat Jul 08, 2006 4:09 am
- Location: Melbourne, Australia
- Contact:
Re: Trim in FILTER stage
Hi,durgaps wrote: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?
Thanks for the reply. I require a solution using FILTER stage, usage of TRANSFORMER is not planned/proposed. Please help.
Durga Prasad
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Last edited by ray.wurlod on Mon Jul 24, 2006 10:00 pm, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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 ?
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 ?
alphac
This is a nice study over filter stage on your first post alphac. :Dalphac wrote: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.
.....
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'