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?
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 ....
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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 ...
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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?
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.
NageshSunkoji
If you know anything SHARE it.............
If you Don't know anything LEARN it...............
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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
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 ...