DSXchange

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

Um... don't insert NULLs?

Can you make use of NVL in your source query? It's not generally a good idea to try to aggregate nulls.

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,

Hi,

Aggregator expects the data to be sorted in the order that is mentioned in Sort tab in the aggregator. If data is not in the order, you would get 'Row out of sequence error' so check the order of your input data by running the query in any SQL tool.

Hope this would help.

Regards
Saravanan

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

Can the order by and group by be done in the Oracle SQL and eliminate the Aggregator?

trobinson wrote:Can the order by and group by be done in the Oracle SQL and eliminate the Aggregator?

Absolutely

Ogmios

Not if some of the aggregating data are generated within the ETL process itself.

ray.wurlod wrote:Not if some of the aggregating data are generated within the ETL process itself.

You can always split up a job in multiple jobsteps. With a little bit of tinkering you can always find ways around DataStage

Hi,
I am getting the same error , "Row Out of Seq". In my SQL Query I am using NVL(column,NULL) , will this be an issue!
Thanks,

vinaymanchinila wrote:In my SQL Query I am using NVL(column,NULL)

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')?

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.

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

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.

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 also wrote:Thats a long way too as I have 68 columns !

Well, you only need to worry about the 'Grouping' columns, not any you are aggregating. Hopefully that's a smaller number than 68.

I donot have any ORDER BY in my query, and yep I need to do the grouping on all 68 columns !
Thanks for your help .

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.