Row out of sequence error

[goriparthi]

Guys, This is regarding the Aggregator problem and i have read previous posts on this , but my problem is i get a row out of sequence error
from what i have understood in the previous posts this error will be coming when u are not sending the correct sort order for your aggregator ,
but i couldnt think of any other way , i tried a lot of different combinations on this sort and nothing works out, ALso i have a huge volume
of data that should be aggregated which is around 50 million everyday. but this job is aborting around 6 hrs after reading everything from source
and sorting & giving 50000 rows only.

Is there any work around for this or am i missing somthing here.This is th sql i used for the initial aggregation in database ,this sql brings down
my source records to 13 million from 50 million in source .Other than this aggregation there is nothing in the job its a straight load into the table
is there any other way that i can fine tune my sql to do the next aggregation also in the database.

(SELECT
a.D_RPT_DATE,
a.I_PART_DIM,
a.I_PART,
b.I_DATE_DIM,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG,
sum(a.I_SALE_QTY) AS I_SALE_QTY,
0 AS I_RET_QTY,
sum(a.A_EXTND_PRICE) AS A_SALE_PRICE,
0 AS A_RET_PRICE


FROM

dlrob.smratrn a ,
dlrob.smrcaln b ,
dlrob.smrdlrd c ,
dlrob.smrtrnt d ,
dlrob.smrtran e ,
dlrob.smrsvhl f

where a.D_RPT_DATE =b.D_DATE
AND a.I_DLR_DIM=c.I_DLR_DIM
AND a.I_TRNT_DIM=d.I_TRNT_DIM
AND a.I_TRAN_DIM=e.I_TRAN_DIM
AND a.I_DSVHL_DIM = f.I_DSVHL_DIM
AND a.d_rpt_date between '07/03/2006' and current date
AND d.C_MOP_NMOP_TYPE IN('MOP','NMOP')
AND e.C_OPSPAY_TYPE IN ('C','W','I',' ')
AND d.C_TRAN_TYPE IN ('W','R','I',' ')
AND a.I_SALE_QTY >= 0
AND a.A_EXTND_PRICE >= 0

group by
a.I_PART,
a.D_RPT_DATE,
a.I_PART_DIM,
b.I_DATE_DIM,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG
order by
a.I_PART,
a.D_RPT_DATE,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG)

UNION ALL

(SELECT
a.D_RPT_DATE,
a.I_PART_DIM,
a.I_PART,
b.I_DATE_DIM,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG,
0 AS I_SALE_QTY,
sum(a.I_SALE_QTY) AS I_RET_QTY,
0 AS A_SALE_PRICE,
sum(a.A_EXTND_PRICE) AS A_RET_PRICE


FROM

dlrob.smratrn a ,
dlrob.smrcaln b ,
dlrob.smrdlrd c ,
dlrob.smrtrnt d ,
dlrob.smrtran e ,
dlrob.smrsvhl f

where a.D_RPT_DATE =b.D_DATE
AND a.I_DLR_DIM=c.I_DLR_DIM
AND a.I_TRNT_DIM=d.I_TRNT_DIM
AND a.I_TRAN_DIM=e.I_TRAN_DIM
AND a.I_DSVHL_DIM = f.I_DSVHL_DIM
AND a.d_rpt_date between '07/03/2006' and current date
AND d.C_MOP_NMOP_TYPE IN('MOP','NMOP')
AND e.C_OPSPAY_TYPE IN ('C','W','I',' ')
AND d.C_TRAN_TYPE IN ('W','R','I',' ')
AND a.I_SALE_QTY < 0
AND a.A_EXTND_PRICE < 0

group by
a.I_PART,
a.D_RPT_DATE,
a.I_PART_DIM,
b.I_DATE_DIM,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG
order by
a.I_PART,
a.D_RPT_DATE,
d.C_MOP_NMOP_TYPE,
d.C_TRAN_TYPE,
e.C_OPSPAY_TYPE,
f.C_DCX_VHCL_FLAG)



These are the SORT properties used in the sort stage and same order is given in aggregator stage.

I_PART ASC,I_RPT_DATE_DIM ASC,C_MOP_NMOP_TYPE ASC,C_TRAN_TYPE ASC,C_OPSPAY_TYPE ASC,C_DCX_VHCL_FLAG ASC
10000
#pOut_Dir#LOG
\
0
no
,
10


Any help in this matter is higly appreciated .


Thanks

Raj

[ray.wurlod]

Lost the Sort stage, rely upon the ORDER BY clause in the SQL to sort the data properly. Ensure that your data types match absolutely.

[meena]

Hi Raj,
How much time this query take when you run it outside the Datastage(mean in CLP or in any tool).

[goriparthi]

HI,

I never ran it outside datasage for all the records , i will try it and give you the timing.is there anyway that i can fine tune my sql.

Also as Ray mentiones i tried the option earlier but as i am giving two sets as unionall my query returns for example

a b 1 2 3 4
a b 2 3 4 5

the above when its positive and the below for negative condition.
for some reason job aborts with the same row out of sequence error
there also even if i remove the sort stage and relied on orderby clause.


Thanks
RAJ

Hi Raj,
How much time this query take when you run it outside the Datastage(mean in CLP or in any tool).

[ray.wurlod]

The UNION ALL ought not to matter, since the ORDER BY is applied after the rows are selected.

Involve your support provider - you may need a patch or an upgrade. I have not encountered this particular message except when the rows genuinely were not in the order the developer asserted (on the input link of the Aggregator stage).

[Kirtikumar]

In your query, if you add order by clause before the union operator then I think it will change the order of output. In DB2 at least it works like this. So a query

Code: Select all

 (select * from emp where DeptNo=10 order by empno)
UNION
(select * from emp where DeptNo=20 order by empno)

, will not give the output in the empNo order.

If you want to have the ordered data after union, you will have to use query as follows:

Code: Select all

select * from 
(
     (select * from emp where DeptNo=10)
  UNION
    (select * from emp where DeptNo=20)
)
as TempTable order by EmpNo

So you will have to order by once the data is unioned.

[kumar_s]

If you need to use Sort stage, leave out the ORDER BY in your query. And what error msg you are refering to?

[goriparthi]

Test_SUMM_SMRMNMP_daily..Aggr_Sales: Row out of sequence
At row 90601, link "DSLink94"
Row out of sequence

This is the error what i have recieved . Thats the only error i got in my log.

If you need to use Sort stage, leave out the ORDER BY in your query. And what error msg you are refering to?

[goriparthi]

Thanks Kumar
I Will try this out.

In your query, if you add order by clause before the union operator then I think it will change the order of output. In DB2 at least it works like this. So a query

Code: Select all

 (select * from emp where DeptNo=10 order by empno)
UNION
(select * from emp where DeptNo=20 order by empno)

, will not give the output in the empNo order.

If you want to have the ordered data after union, you will have to use query as follows:

Code: Select all

select * from 
(
     (select * from emp where DeptNo=10)
  UNION
    (select * from emp where DeptNo=20)
)
as TempTable order by EmpNo

So you will have to order by once the data is unioned.

[chulett]

for some reason job aborts with the same row out of sequence error there also even if i remove the sort stage and relied on orderby clause.

Bottom line - it aborts with a 'Row out of Sequence' error because (oddly enough) your rows are out of sequence. Whatever order you are sorting the incoming data (doesn't matter if by Sort stage or Order By) does not match the Sort information you've asserted in the Aggregator stage. Period.

Sometimes it's not at all obvious. It can depend on what metadata you've used in the Aggregator and string sorts versus pure numeric sorts. Sometimes you need to zero pad numeric fields out to a consistent length. You need to go through the data you are sending in to the Aggregator with a fine tooth comb. If you know how far it gets into the data before the error occurs, start looking there.

I've done this investigation in the past by first sending the sorted key data to a flat file rather than the Aggregator. Don't assume a bug is at work here... it's your data.

[DSguru2B]

Great piece of advice from Craig. If i were you i would start looking at the erraneous row.

[goriparthi]

Thanks a lot ,

It worked out for me , Basically i am using aggregator to do a group by after the result set but now i am doing it in the database itself so need of aggregator but i will continue investigating with the aggregator as craig mentioned.



Thanks
Raj

In your query, if you add order by clause before the union operator then I think it will change the order of output. In DB2 at least it works like this. So a query

Code: Select all

 (select * from emp where DeptNo=10 order by empno)
UNION
(select * from emp where DeptNo=20 order by empno)

, will not give the output in the empNo order.

If you want to have the ordered data after union, you will have to use query as follows:

Code: Select all

select * from 
(
     (select * from emp where DeptNo=10)
  UNION
    (select * from emp where DeptNo=20)
)
as TempTable order by EmpNo

So you will have to order by once the data is unioned.

[ray.wurlod]

That may have been faster as

Code: Select all

 SELECT * FROM emp WHERE DEPT_NO IN (10,20) ORDER BY EMP_NO