Row out of sequence - Error
Moderators: chulett, rschirm, roy
Row out of sequence - Error
Hi,
I am getting this error in my aggregator.
Short description of the problem:
My aggregator gets input from an Oracle OCI stage where I fetch the rows from the oracle database ordered by four different columns. In the aggregator I have specified the sort order of the input rows as the same which I did in the Oracle. Then I group by all these four columns. The job runs fine if these input columns have values. If I insert a NULL into any of these four columns in the input, the Aggregator is failing during the runtime saying an error "Row out of sequence".
Whats the solution for this?
Thanks in advance!
Venkatesh
I am getting this error in my aggregator.
Short description of the problem:
My aggregator gets input from an Oracle OCI stage where I fetch the rows from the oracle database ordered by four different columns. In the aggregator I have specified the sort order of the input rows as the same which I did in the Oracle. Then I group by all these four columns. The job runs fine if these input columns have values. If I insert a NULL into any of these four columns in the input, the Aggregator is failing during the runtime saying an error "Row out of sequence".
Whats the solution for this?
Thanks in advance!
Venkatesh
Hi,
In case you want NULLs in your target the message you get simply says that the aggregator stage logic of the order by is not consistant with the input data logic.
so you have 2 choices:
1. as Craig said transform the NULL values for the agg stage, if NULLs are to be put in the target use a transformer to retransform then to NULLs prior to loading them to your target.
2. make the source order by to match the agg stage's logic, if possible.
Good Luck,
In case you want NULLs in your target the message you get simply says that the aggregator stage logic of the order by is not consistant with the input data logic.
so you have 2 choices:
1. as Craig said transform the NULL values for the agg stage, if NULLs are to be put in the target use a transformer to retransform then to NULLs prior to loading them to your target.
2. make the source order by to match the agg stage's logic, if possible.
Good Luck,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
![Image](http://www.worldcommunitygrid.org/images/logo.gif)
Row out of sequence - Error
Hi there
Just thought I would add a small solution to something that caught me out with a sort stage feeding a aggregator...
I found that case sensitivity on the input data caused an issue. I specified a "FIELD1 A, FIELD2 A" type sort on my Sort stage but as FIELD1 contained both upper and lowercase characters, the data sorted by the sort stage was rejected by the aggregator with a 'Row out of sequence' error.
I changed the sort stage to be "FIELD1 a, FIELD2 A" and it now works fine. Note the lowercase "a" after FIELD1.
Capital "A" being used for a case-insensitive sorts and a Lowercase "a" for case-sensitive sorts.
This is probably obvious to some of you but I thought it worthwhile sharing - for the benefit of the other DS novices out there.
Cheers
Tim
Just thought I would add a small solution to something that caught me out with a sort stage feeding a aggregator...
I found that case sensitivity on the input data caused an issue. I specified a "FIELD1 A, FIELD2 A" type sort on my Sort stage but as FIELD1 contained both upper and lowercase characters, the data sorted by the sort stage was rejected by the aggregator with a 'Row out of sequence' error.
I changed the sort stage to be "FIELD1 a, FIELD2 A" and it now works fine. Note the lowercase "a" after FIELD1.
Capital "A" being used for a case-insensitive sorts and a Lowercase "a" for case-sensitive sorts.
This is probably obvious to some of you but I thought it worthwhile sharing - for the benefit of the other DS novices out there.
Cheers
Tim
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
I'm sorry, but what would be the point of doing that? NVL substitutes another value for nulls in the field you are querying and you are telling Oracle that, when your column is null, to pass the null. Might as well not have the NVL function in your query at all. Unless you meant NVL(column,'NULL')?vinaymanchinila wrote:In my SQL Query I am using NVL(column,NULL)
![Confused :?](./images/smilies/icon_confused.gif)
The sequence error means simply that the order you told the stage the data would be sorted in does not match what you actually sent it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
Hi Craig,
I meant (column,'NULL'), looong weekend in office!
So by checking the order in the sort stage and the properties box where we manually provide like Col1 ASC, Col2 ASC the error can be resolved.
Thats a long way too as I have 68 columns !
One more thing what would be the differnece in using SORT stage versus AGGREVATOR to gruop the data?
Thanks a bunch
I meant (column,'NULL'), looong weekend in office!
So by checking the order in the sort stage and the properties box where we manually provide like Col1 ASC, Col2 ASC the error can be resolved.
Thats a long way too as I have 68 columns !
One more thing what would be the differnece in using SORT stage versus AGGREVATOR to gruop the data?
Thanks a bunch
Yes - as long as they match! Keep in mind your ORDER BY clause will need to do the NVL as well if you are sorting on that column.vinaymanchinila wrote:So by checking the order in the sort stage and the properties box where we manually provide like Col1 ASC, Col2 ASC the error can be resolved.
Well, you only need to worry about the 'Grouping' columns, not any you are aggregating. Hopefully that's a smaller number than 68.vinaymanchinila also wrote:Thats a long way too as I have 68 columns !
![Wink :wink:](./images/smilies/icon_wink.gif)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 353
- Joined: Wed Apr 06, 2005 8:45 am
If you don't have an ORDER BY clause in your SQL then you shouldn't be asserting the data is sorted in the Aggregator. Unless there is a Sort stage involved...
Why not let the database do the grouping for you? Unless you are grouping on fields derived in the job, your source database should be able to make faster work of it.
Why not let the database do the grouping for you? Unless you are grouping on fields derived in the job, your source database should be able to make faster work of it.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers