Posted by metaperl on 11/29/07 15:12

Far below (in section "original 3 steps"), you see the following:
1. a temp table is created
2. some data is inserted into this table
3. some of the inserted data is removed based on a join with the same
table that the original select was made from

In my opinion, there is no way that the join could produce more rows
than were originally retrieved from viewD. Hence, we could get rid of
the DELETE step by simply changing the query to be:

INSERT INTO #details ( rec_id, orig_corr, bene_corr )
SELECT rec_id, 0, 0
FROM viewD
WHERE SOURCE_SYS NOT IN ( 'G', 'K' )
AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )
AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )
AND INMESS NOT LIKE '2__' ---- the added line

===== original 3 steps (mentioned above) =====

CREATE TABLE #details (
rec_id UNIQUEIDENTIFIER PRIMARY KEY NOT NULL,
orig VARCHAR(35) NULL,
bene VARCHAR(35) NULL,
orig_corr TINYINT NULL,
bene_corr TINYINT NULL
)

INSERT INTO #details ( rec_id, orig_corr, bene_corr )
SELECT rec_id, 0, 0
FROM viewD
WHERE SOURCE_SYS NOT IN ( 'G', 'K' )
AND MONTH( VALUE_DATE_A8 ) = MONTH( @date )
AND YEAR( VALUE_DATE_A8 ) = YEAR( @date )

DELETE d
FROM #details d
JOIN viewD v ON ( d.rec_id = v.rec_id )
WHERE INMESS LIKE '2__'

[Back to original message]