|
|
|
Forum Newbie
Group: Forum Members
Last Login: Today @ 3:53:28 AM
Posts: 5,
Visits: 22
|
|
DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE EXISTS(SELECT '1' FROM
L_DLYBCIDDB.CREDIT_ENTRIES_TEMP T2
WHERE T1.MI_ACCOUNT_IDENTIFIER = T2.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = T2.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = T2.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = T2.ENTRY_CODE
AND T1.ENTRY_DATE <= T2.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = T2.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;
CREDIT_ENTRIES_CT has nearly 2.2 billon rows.
CREDIT_ENTRIES_TEMP has nearly 6 million rows.
i have all the recommended stats.can we change the query ?Please help .....
R LAL
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Step 1 - Get rid of the correlated subquery by:
DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND ( (T1.ENTRY_SOURCE_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_CODE
AND T1.ENTRY_DATE <= L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_DATE )
OR T1.ENTRY_SEQUENCE_NUMBER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SEQUENCE_NUMBER)
)
AND T1.ENTRY_AMOUNT_CODE = 4;
If no better, post the DDL for the two tables and an explain by putting a SHOW in front of the query.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Today @ 3:53:28 AM
Posts: 5,
Visits: 22
|
|
Thanks for ur valuable suggestion, but there is no much change in the CPU time, efficiency.
The DDL's for the two tables are the following.
Table 1:
======
CREATE MULTISET TABLE A_BCIDDB.CREDIT_ENTRIES_CT ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
FREESPACE = 15 PERCENT,
CHECKSUM = DEFAULT
(
MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('D01','D02','D03','D04','D16','N03','N04','N05','N07','N10','N17','N30','N32','N37'),
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS (4 ,6 ),
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS (15 ,20 ,22 ,35 ,36 ,37 ,38 ,40 ,43 ,44 ,45 ,50 ,60 ,61 ,65 ),
ENTRY_CODE SMALLINT NOT NULL COMPRESS (10 ,12 ,14 ,15 ,20 ,21 ,23 ,27 ,40 ,41 ,45 ,59 ,60 ),
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS (285 ,83 ,84 ,85 ,185 ),
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS (0 ,1 ,2 ,3 ,5 ,6 ,7 ,8 ,-6 ),
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS (100.00 ,200.00 ,3.00 ,50.00 ,4.00 ,30.00 ,25.00 ,2.00 ,20.00 ,15.00 ,10.00 ,5.00 ),
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('C','M','U'),
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ),
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL DEFAULT 0 COMPRESS (0 ,1 ,2 ,3 ))
PRIMARY INDEX ( MI_ACCOUNT_IDENTIFIER ,ENTRY_DATE ,ENTRY_SEQUENCE_NUMBER );
Table 2:
======
CREATE MULTISET TABLE L_DLYBCIDDB.CREDIT_ENTRIES_TEMP ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
FREESPACE = 15 PERCENT,
CHECKSUM = DEFAULT
(
MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL
,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'D01',
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL
COMPRESS (DATE '2002-03-28'),
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS 4 ,
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS 20 ,
ENTRY_CODE SMALLINT NOT NULL COMPRESS 45 ,
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS 84 ,
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS 6 ,
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 100.00 ,
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN
NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'C',
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS 0 ,
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL COMPRESS 0,
UNIQUE_ID INTEGER NOT NULL)
PRIMARY INDEX (MI_ACCOUNT_IDENTIFIER ,ENTRY_DATE ,ENTRY_SEQUENCE_NUMBER);
Explain plan:This is for a sample of 3 million rows for table1 and 5000 rows in table2, but actually there are more than 2 billion rows in table1 and 4 million in table2.So it is taking 4 hrs to run.
======================================================================
1) First, we lock a distinct A_gis_centraldb."pseudo table" for write
on a RowHash to prevent global deadlock for A_gis_centraldb.T1.
2) Next, we lock a distinct A_gis_centraldb."pseudo table" for read
on a RowHash to prevent global deadlock for A_gis_centraldb.T2.
3) We lock A_gis_centraldb.T1 for write, and we lock
A_gis_centraldb.T2 for read.
4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from A_gis_centraldb.T1 by
way of an all-rows scan with a condition of (
"A_gis_centraldb.T1.ENTRY_AMOUNT_CODE = 4") into Spool 2
(all_amps) (compressed columns allowed), which is
redistributed by hash code to all AMPs. Then we do a SORT to
order Spool 2 by row hash. The size of Spool 2 is estimated
with high confidence to be 2,993,685 rows. The estimated
time for this step is 0.57 seconds.
2) We do an all-AMPs RETRIEVE step from A_gis_centraldb.T2 by
way of an all-rows scan with no residual conditions into
Spool 3 (all_amps), which is redistributed by hash code to
all AMPs. Then we do a SORT to order Spool 3 by row hash and
the sort key in spool field1 eliminating duplicate rows. The
size of Spool 3 is estimated with high confidence to be 4,554
rows. The estimated time for this step is 0.02 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of an
all-rows scan, which is joined to Spool 3 (Last Use) by way of an
all-rows scan. Spool 2 and Spool 3 are joined using an inclusion
merge join, with a join condition of ("(((ENTRY_SOURCE_CODE =
ENTRY_SOURCE_CODE) AND ((ENTRY_CODE = ENTRY_CODE) AND (ENTRY_DATE
<= ENTRY_DATE ))) OR (ENTRY_SEQUENCE_NUMBER =
ENTRY_SEQUENCE_NUMBER )) AND ((ENTRY_AMOUNT = ENTRY_AMOUNT) AND
(MI_ACCOUNT_IDENTIFIER = MI_ACCOUNT_IDENTIFIER ))"). The result
goes into Spool 1 (all_amps), which is redistributed by hash code
to all AMPs. Then we do a SORT to order Spool 1 by row hash and
the sort key in spool field1 eliminating duplicate rows. The size
of Spool 1 is estimated with index join confidence to be 4,547
rows. The estimated time for this step is 0.41 seconds.
6) We do an all-AMPs MERGE DELETE to A_gis_centraldb.T1 from Spool 1
(Last Use) via the row id.
7) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
NOTE: The databases in the explain plan is the test database.
Thanks in advance
R LAL
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
The Primary Index on both these tables do not make sense, at least for this job. The suggested index is far more paractical, as long as spread is OK.
I enclose DDL for suggested primary index for both tables - you will need to set up copies using this DDL and load them with test data, then try the query shown. (Send a Show if any problems, as before.)
The problem is arising because both tables have to be redistributed before the query will run. Also, I have converted your OR'd join to two separate Deletes, using a multi-statement request.
After loading the data, check the distribution to make sure it is fairly even.
It may be worth suggesting that someone take a look at your physical design!
CREATE MULTISET TABLE A_BCIDDB.CREDIT_ENTRIES_CT
( MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('D01','D02','D03','D04','D16','N03','N04','N05','N07','N10','N17','N30','N32','N37'),
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS (4 ,6 ),
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS (15 ,20 ,22 ,35 ,36 ,37 ,38 ,40 ,43 ,44 ,45 ,50 ,60 ,61 ,65 ),
ENTRY_CODE SMALLINT NOT NULL COMPRESS (10 ,12 ,14 ,15 ,20 ,21 ,23 ,27 ,40 ,41 ,45 ,59 ,60 ),
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS (285 ,83 ,84 ,85 ,185 ),
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS (0 ,1 ,2 ,3 ,5 ,6 ,7 ,8 ,-6 ),
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL
COMPRESS (100.00 ,200.00 ,3.00 ,50.00 ,4.00 ,30.00 ,25.00 ,2.00 ,20.00 ,15.00 ,10.00 ,5.00 ),
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ('C','M','U'),
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS (0 ,1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10 ),
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL DEFAULT 0 COMPRESS (0 ,1 ,2 ,3 ))
PRIMARY INDEX ( MI_ACCOUNT_IDENTIFIER );
CREATE MULTISET TABLE L_DLYBCIDDB.CREDIT_ENTRIES_TEMP
( MI_ACCOUNT_IDENTIFIER DECIMAL(10,0) NOT NULL,
ENTRY_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL,
ENTRY_TIME CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
ENTRY_SOURCE_SYSTEM_CODE CHAR(3) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'D01',
ENTRY_SEQUENCE_NUMBER CHAR(14) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL,
ENTRY_POSTING_DATE DATE FORMAT 'YYYY-MM-DD' NOT NULL
COMPRESS (DATE '2002-03-28'),
ENTRY_REJECT_CODE SMALLINT NOT NULL COMPRESS 0 ,
REJECTED_ENTRY_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIGINATOR_ACCOUNT_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT_CODE SMALLINT NOT NULL COMPRESS 4 ,
ENTRY_SOURCE_CODE SMALLINT NOT NULL COMPRESS 20 ,
ENTRY_CODE SMALLINT NOT NULL COMPRESS 45 ,
ENTRY_2B_CODE SMALLINT NOT NULL COMPRESS 84 ,
ENTRY_2B_TLA SMALLINT NOT NULL COMPRESS 6 ,
ENTRY_DESCRIPTION INTEGER NOT NULL COMPRESS 0 ,
ENTRY_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 100.00 ,
ENTRY_CANCELLED_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ORIG_BENEFICIARY_BRANCH_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ORIG_BENEFICIARY_ACC_NUMBER INTEGER NOT NULL COMPRESS 0 ,
ENTRY_SOURCE_RECORD_REFERENCE CHAR(4) CHARACTER SET LATIN
NOT CASESPECIFIC COMPRESS ' ',
BENEFICIARY_IDENTIFIER INTEGER NOT NULL COMPRESS 0 ,
CLEARED_NOTICE_INDICATOR CHAR(1) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS 'C',
ENTRY_NARRATIVE_ONE CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
ENTRY_NARRATIVE_TWO CHAR(18) CHARACTER SET LATIN
NOT CASESPECIFIC NOT NULL COMPRESS ' ',
SOURCE_SUB_BRANCH_IDENTIFIER SMALLINT NOT NULL COMPRESS 0 ,
SOURCE_BRANCH_SORT_CODE INTEGER NOT NULL COMPRESS 0 ,
ITEM_COUNT INTEGER NOT NULL COMPRESS 0 ,
CASH_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ZERO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_ONE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_TWO_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_THREE_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
UNCLEARED_DAY_FOUR_AMOUNT DECIMAL(15,2) NOT NULL COMPRESS 0.00 ,
REVERSAL_CODE BYTEINT NOT NULL COMPRESS 0,
UNIQUE_ID INTEGER NOT NULL)
PRIMARY INDEX (MI_ACCOUNT_IDENTIFIER );
DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT_CODE = 4;
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND T1.ENTRY_SOURCE_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SOURCE_CODE
AND T1.ENTRY_CODE = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_CODE
AND T1.ENTRY_DATE <= L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_DATE
; DELETE FROM A_BCIDDB.CREDIT_ENTRIES_CT T1
WHERE T1.MI_ACCOUNT_IDENTIFIER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.MI_ACCOUNT_IDENTIFIER
AND T1.ENTRY_AMOUNT_CODE = 4;
AND T1.ENTRY_AMOUNT = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_AMOUNT
AND T1.ENTRY_SEQUENCE_NUMBER = L_DLYBCIDDB.CREDIT_ENTRIES_TEMP.ENTRY_SEQUENCE_NUMBER
;
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Today @ 3:53:28 AM
Posts: 5,
Visits: 22
|
|
Thanku worked some what better.
R LAL
|
|
|
|