The above Error occurs when the aggregator option "Sorted Input" is enabled and the data is being sorted by the source DB Engine.
This is caused when the Source DB's and Informatica's Collation are different thus aggregator raises an error when it receives data in a different from expected order. As you Can see at the image below character "_" is collated before char "S" by default.
Informatica expects the opposite since it is using binary sort.
In order to fix that uses the corresponding DB clause to utilize the proper collation. In that particular example we are using Sybase IQ and the solution is to change order by clause
From:
Order By DetailTbl.AST_KEY,
DetailTbl.AST_VALUE_TYPE,
DetailTbl.AS_ID_TIME_BY_DAY_DT
To:
Order By DetailTbl.AST_KEY,
SORTKEY(DetailTbl.AST_VALUE_TYPE, 'binary'),
DetailTbl.AS_ID_TIME_BY_DAY_DT
if we issue the query with the altered order by clause we get the order that Infa expects.
Some rules that apply:
This is caused when the Source DB's and Informatica's Collation are different thus aggregator raises an error when it receives data in a different from expected order. As you Can see at the image below character "_" is collated before char "S" by default.
Informatica expects the opposite since it is using binary sort.
In order to fix that uses the corresponding DB clause to utilize the proper collation. In that particular example we are using Sybase IQ and the solution is to change order by clause
From:
Order By DetailTbl.AST_KEY,
DetailTbl.AST_VALUE_TYPE,
DetailTbl.AS_ID_TIME_BY_DAY_DT
To:
Order By DetailTbl.AST_KEY,
SORTKEY(DetailTbl.AST_VALUE_TYPE, 'binary'),
DetailTbl.AS_ID_TIME_BY_DAY_DT
Some rules that apply:
- A character set is a set of symbols and encodings.
- A collation is a set of rules for comparing characters in a character set.
- Any given character set always has at least one collation.
- Any given character set may have several collations.
