Filter Stage

ajith · Post by **ajith** » Thu Dec 07, 2006 1:19 am

Hi,

My job flow is like this


                          Oracle stage
                                 |
                                 |
                           Modify Stage
                                 |
                                 |
Seq ----   Filter1 --- Look Up----- Flter 2 ------
                                        |
                                        |
                                       Upd File

All my Seq file are fixed width with all fields of Char Datatype ..
In the modify stage I am changing the datatyes to Char ....

The look up fail condition is Continue and I need to filter on the records which has a match on 2 nd filter file ...

Since All the fields have Char datatype it will be autopaded by spaces .. But from the first filter I get to know that Filter is doing an auto trim on the column value on which I am filtering
ie ,

If CODE is of Char (10) and if I write the condition as CODE='AA' it works perfectly fine.

I wanted to write the condition for the second filter and I want to know whether It is all right if I give a column DESC which is coming oracle stage to one space

ie DESC = ' '

Why is this happening? ...

Is there any Environment variables to specify the behavior of the Filter stage down there?

Curious ....

Ajith

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 1:32 am

Check if APT_PAD_STRING is ' '. Try to change it to 0x0 (Null). May be a case where empty space might be considered as pad chars. Try if that works.

ajith · Post by **ajith** » Thu Dec 07, 2006 1:40 am

kumar_s wrote:Check if APT_PAD_STRING is ' '. Try to change it to 0x0 (Null). May be a case where empty space might be considered as pad chars. Try if that works.

Cannot afford to do that as The files I am writing into are fixed width files padded with spaces at the end ....

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 1:47 am

What is the length and datatype of DESC?
If it is not char from database, and if you pad some char in Datastage, you cannot check for single character.

ajith · Post by **ajith** » Thu Dec 07, 2006 1:54 am

DESC is Varchar from Database and I pad it with spaces ...

Anyway, I want to filter it when there is no match for the key, ie in case of lookup failure ... I was using Is Null Desc ... But that does not work ...
Since The null value is getting padded by spaces after the lookup ...

Hope my point is clear ....

I want to filter the records which has no match from the oracle stage ...

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 1:59 am

This case you can check for the space for the full length of the DESC field. Or you can trim and check for ''.

ajith · Post by **ajith** » Thu Dec 07, 2006 3:01 am

kumar_s wrote:This case you can check for the space for the full length of the DESC field. Or you can trim and check for ''.

No, That is exactly my point, I just have to compare with one space ...

The filter automatically trims ( Ignores ) rest of the spaces ...

Is this a bug with the filter stage??? ...

I want to know why this happens ...

Thanks,

Ajith

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 3:05 am

How can Filter stage can diffrentiate, if the space is from the source or that been padded by previous stage? Or you check for the presence of space in the first char of the DESC.

ajith · Post by **ajith** » Thu Dec 07, 2006 3:14 am

kumar_s wrote:How can Filter stage can diffrentiate, if the space is from the source or that been padded by previous stage? Or you check for the presence of space in the first char of the DESC.

Filter stage does not differentiate ... It just looks for Characters other than the padchar at the end of the string and compares.

At the same time It is not ignoring the spaces at the start.

Here are the scenarios
Input
"DESC = 'Hello ' "
Filter Condition
"DESC = 'Hello' "
It meets the Filter condition

Input
"DESC = ' Hello ' "
Filter Condition
"DESC = 'Hello' "
It does not meet the filter condition

Input
DESC = Space(255)
Filter Condition
DESC = ' '
It meets the condition ...

I want to know why this happens ...As Kumar pointed out how does it know where the spaces are from?

Nageshsunkoji · Post by **Nageshsunkoji** » Thu Dec 07, 2006 3:24 am

Hi Ajith,

Here you are performing lookup and taking the field DESC from reference is it right ?

If your lookup fails, it have to give Null in the filed DESC.
Here my question is what is the nullability of filed DESC ( NO/YES).
If it is NO, datastage will set ' ' for the DESC filed and check If it is YES, then datastage provide you a NULL, then check the Isnull condition in Filter stage and one more thing, you said that DESC is Varchar from Database and you padded with spaces, where you have done this ? Is it Oracle stage is EE stage then use the APT_STRING_PADCHAR and check the value 0x20 for spaces.

kumar_s · Post by **kumar_s** » Thu Dec 07, 2006 3:24 am

Hi Ajith,
Padchar pads the given character till the given length. Hence if say length is 10, and if the input is ' Hello' the result will be ' Hello '. So the filter will facilitate the check by assuming all the chars that present in the right of the character will be due to the padded character. Which you have set in Environmental variable. So if you sure that there wont be any space available in left of any data, you can directly check the ' ' for the given field. Else you need to do some work around.

ajith · Post by **ajith** » Thu Dec 07, 2006 3:36 am

Nageshsunkoji wrote:Hi Ajith,

Here you are performing lookup and taking the field DESC from reference is it right ?

If your lookup fails, it have to give Null in the filed DESC.
Here my question is what is the nullability of filed DESC ( NO/YES).
If it is NO, datastage will set ' ' for the DESC filed and check If it is YES, then datastage provide you a NULL, then check the Isnull condition in Filter stage and one more thing, you said that DESC is Varchar from Database and you padded with spaces, where you have done this ? Is it Oracle stage is EE stage then use the APT_STRING_PADCHAR and check the value 0x20 for spaces.

Yeah, This makes sense , I kept it not nullable ...
But filter stage is doin a crime by ignoring trailing all APT_STRING_PADCHAR vales...

Does not cause a concern for me but then there will be people who will suffer ...

Thanks all, especially Nagesh and Kumar
Ajith