Row out of sequence error
Posted: Wed Oct 25, 2006 8:23 am
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
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