Alternative to cursor

Question

Alternative to cursor

pbb001

Ten Centuries

Points: 1068
More actions
December 5, 2003 at 5:39 am

#107018

Hello all. We have a table with document info. that contains copy letters and originals. You should never have copy without original. I had to produce a list of accounts that have a copy but no matching original. I have managed to do this using a cursor (shown below) but was wondering if there was a neater way of doing it without having to use a cursor. Thanks.
DECLARE @class varchar(19)
DECLARE @account varchar(10)
DECLARE @cust varchar(10)
DECLARE @doc_type varchar(5)
CREATE TABLE #tbl_tmp
(class varchar(19), account varchar(10), cust varchar(10), doc_type varchar(5))
DECLARE db_curs CURSOR
FOR SELECT class, account, cust, doc_type FROM documents
WHERE class = 'DOC'
AND doc_type IN ('COPY1', 'COPY2')
OPEN db_curs
FETCH NEXT FROM db_curs INTO @class, @account, @cust, @doc_type
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tbl_tmp
SELECT DISTINCT class, account, cust, doc_type FROM documents
WHERE account = @account
AND class = 'DOC'
AND doc_type IN ('COPY1', 'COPY2')
AND NOT EXISTS (SELECT class, account, cust, doc_type FROM documents
WHERE account = @account
AND class = 'DOC'
AND doc_type IN ('ORIG1', 'ORIG2'))
FETCH NEXT FROM db_curs INTO @class, @account, @cust, @doc_type
END
SELECT * FROM #tbl_tmp
DROP TABLE #tbl_tmp
CLOSE db_curs
DEALLOCATE db_curs

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jonathan SSC-Insane Points: 20427 More actions · Answer 1



SELECT c.Class, c.Account, c.Cust, c.Doc_Type

FROM Documents c LEFT JOIN Documents o ON o.Account = c.Account AND o.Doc_Type IN ('ORIG1','ORIG2')

WHERE c.Doc_Type IN ('COPY1','COPY2')

AND o.Account IS NULL

--Jonathan

David Burrows SSC Guru Points: 65144 More actions · Answer 2

SELECT a.class, a.account, a.cust, a.doc_type 

FROM documents a 

LEFT OUTER JOIN documents b 

ON b.class = a.class 

AND b.account = a.account 

AND b.cust = a.cust 

AND b.doc_type IN ('ORIG1', 'ORIG2') 

WHERE a.class = 'DOC' 

AND a.doc_type IN ('COPY1', 'COPY2') 

AND b.account IS NULL

Jonathan,

I believe yours will not select anything or the wrong results.

Correction, it will if cust is the same for each account.

Edited by - davidburrows on 12/05/2003 06:37:47 AM

Far away is close at hand in the images of elsewhere.
Anon.

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

quote:
Jonathan,
I believe yours will not select anything or the wrong results.
Correction, it will if cust is the same for each account.

Thanks. Didn't notice the class restriction.

From OP's code, it doesn't appear that Cust is in the primary key, so perhaps that shouldn't be in the join.

--Jonathan

pbb001 Ten Centuries Points: 1068 More actions · Answer 4

Thanks both. Results match mine but your way is obviously a lot quicker and neater. More practice needed on joins for me I think!

Paul

David Burrows SSC Guru Points: 65144 More actions · Answer 5

quote:
From OP's code, it doesn't appear that Cust is in the primary key, so perhaps that shouldn't be in the join.

Ooops. If results matched then you're right.

Far away is close at hand in the images of elsewhere.
Anon.