Inserted records from cursor are doubling up

[davidgoulet]

I'm are trying to insert invoice record in a table. Where the invoice has more than one line, I'm trying to write each line to a separate page in my journal table. Unfortunately page one need to be ' ', and the next page '001'.. '002', etc.

I'm using an If statement to do this, but what is happening, is that a invoice with 2 lines, is getting inserted twice with a page of ' ', and twice with a page of '001'.

For example invoice OP/I000005 has two lines: This is what gets inserted:

joournal_no,slash, page_no, date ... ,total_debits
OP/I000004 2003-08-05 ... 15.00 OP/I000005 2003-08-05 ... 10.15
OP/I000005 2003-08-05 ... 12.04
OP/I000005 / 001 2003-08-05 ... 10.15
OP/I000005 / 001 2003-08-05 ... 12.04

Here is the code. The insert works, but its inserting duplicate records, when they is more than one line on invoice.


DECLARE @invoice_no char(10)
DECLARE @invoice char(10)
DECLARE @page char(3)
DECLARE @page_no int
DECLARE @slash char(1)

set @invoice = ' '

DECLARE o1 cursor for

select invoice_no from scheme.opdtcosm
where invoice_no not in (select journal_no from scheme.nljaudm)

OPEN o1

FETCH NEXT from o1 into @invoice_no

WHILE @@FETCH_STATUS = 0

BEGIN

IF @invoice <> @invoice_no

BEGIN
set @invoice = @invoice_no
set @page = ' '
set @slash = ' '
set @page_no = 0

insert into scheme.nljrnm_test
select @invoice, @slash , @page, t.journal_date, left(o.cust_name,20), ' ', ' ', o.sl_period, o.sl_period, NULL,
ROUND(journal_amount,2), ROUND(journal_amount,2), ' 5', ' 5', material_gl_acct, labour_gl_acct, overhead_gl_acct, subcontract_gl_acct, t.posting_code,
' ', ' ', ' ', ' ', ' ',
ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2)),
ROUND(labour_cost,2), ROUND(overhead_cost,2), ROUND(subcontract_cost,2), ROUND(journal_amount,2) * -1,
0, 0, 0, 0, 0,
t.vat_code, t.vat_code, t.vat_code, t.vat_code, t.vat_code, ' ', ' ', ' ', ' ', ' ',
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
left(o.cust_name,20),left(o.cust_name,20), left(o.cust_name,20), left(o.cust_name,20), left(o.cust_name,20),
' ', ' ', ' ', ' ', ' ',
' ', t.transaction_date, t.origin, t.batch_reference ,
'CAD', 'CAD', 'CAD', 'CAD', 'CAD', ' ', ' ', ' ', ' ', ' ',
t.currency_code, t.currency_code, t.currency_code, t.currency_code, t.currency_code, ' ', ' ', ' ', ' ', ' ',
t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type,
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P',
t.multidiv, t.multidiv, t.multidiv, t.multidiv, t.multidiv, ' ', ' ', ' ', ' ', ' ',
'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', t.analysis_code1, t.analysis_code2, t.analysis_code2,
(ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2))/ (ROUND(journal_amount,2)/ROUND(currency_amount,2))),
(ROUND(labour_cost,2) /(ROUND(journal_amount,2)/ROUND(currency_amount,2))) , (ROUND(overhead_cost,2) /(ROUND(journal_amount,2)/ROUND(currency_amount,2))),
(ROUND(subcontract_cost,2) / (ROUND(journal_amount,2)/ROUND(currency_amount,2))), ((ROUND(journal_amount,2) * -1) / (ROUND(journal_amount,2)/ROUND(currency_amount,2))),
0, 0, 0, 0, 0,
ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2)),
ROUND(labour_cost,2), ROUND(overhead_cost,2), ROUND(subcontract_cost,2), ROUND(journal_amount,2) * -1,
0, 0, 0, 0, 0,
(ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)),
(ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)), 0, 0, 0, 0, 0,
' ', t.transaction_group, 'N', 0
from scheme.nltranm t, scheme.opdtcosm o
where left(t.journal_number,10) = @invoice
and t.posting_code = left(material_gl_acct,8) + '419'
and ROUND(o.total_cost,2) = ROUND(t.journal_amount,2)
END

ELSE BEGIN

set @invoice = @invoice_no
set @slash = '/'
set @page_no = @page_no + 1
set @page = right(( @page_no * .0001),3)


insert into scheme.nljrnm_test
select @invoice, @slash , @page, t.journal_date, left(o.cust_name,20), ' ', ' ', o.sl_period, o.sl_period, NULL,
ROUND(journal_amount,2), ROUND(journal_amount,2), ' 5', ' 5', material_gl_acct, labour_gl_acct, overhead_gl_acct, subcontract_gl_acct, t.posting_code,
' ', ' ', ' ', ' ', ' ',
ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2)),
ROUND(labour_cost,2), ROUND(overhead_cost,2), ROUND(subcontract_cost,2), ROUND(journal_amount,2) * -1,
0, 0, 0, 0, 0,
t.vat_code, t.vat_code, t.vat_code, t.vat_code, t.vat_code, ' ', ' ', ' ', ' ', ' ',
0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
left(o.cust_name,20),left(o.cust_name,20), left(o.cust_name,20), left(o.cust_name,20), left(o.cust_name,20),
' ', ' ', ' ', ' ', ' ',
' ', t.transaction_date, t.origin, t.batch_reference ,
'CAD', 'CAD', 'CAD', 'CAD', 'CAD', ' ', ' ', ' ', ' ', ' ',
t.currency_code, t.currency_code, t.currency_code, t.currency_code, t.currency_code, ' ', ' ', ' ', ' ', ' ',
t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type, t.currency_type,
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N',
'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P', 'P',
t.multidiv, t.multidiv, t.multidiv, t.multidiv, t.multidiv, ' ', ' ', ' ', ' ', ' ',
'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', 'O', t.analysis_code1, t.analysis_code2, t.analysis_code2,
(ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2))/ (ROUND(journal_amount,2)/ROUND(currency_amount,2))),
(ROUND(labour_cost,2) /(ROUND(journal_amount,2)/ROUND(currency_amount,2))) , (ROUND(overhead_cost,2) /(ROUND(journal_amount,2)/ROUND(currency_amount,2))),
(ROUND(subcontract_cost,2) / (ROUND(journal_amount,2)/ROUND(currency_amount,2))), ((ROUND(journal_amount,2) * -1) / (ROUND(journal_amount,2)/ROUND(currency_amount,2))),
0, 0, 0, 0, 0,
ROUND(journal_amount,2) - (ROUND(labour_cost,2) + ROUND(overhead_cost,2) + ROUND(subcontract_cost,2)),
ROUND(labour_cost,2), ROUND(overhead_cost,2), ROUND(subcontract_cost,2), ROUND(journal_amount,2) * -1,
0, 0, 0, 0, 0,
(ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)),
(ROUND(journal_amount,2)/ROUND(currency_amount,2)), (ROUND(journal_amount,2)/ROUND(currency_amount,2)), 0, 0, 0, 0, 0,
' ', t.transaction_group, 'N', 0
from scheme.nltranm t, scheme.opdtcosm o
where left(t.journal_number,10) = @invoice
and t.posting_code = left(material_gl_acct,8) + '419'
and ROUND(o.total_cost,2) = ROUND(t.journal_amount,2)
END


FETCH NEXT from o1 into @invoice_no

END
CLOSE o1
DEALLOCATE o1

 

[LogicalmanUS]

davidgoulet,

My first piece of advice would be to find out exactly what is being loaded into the cursor.

Place the following code after the initial declare statement, and run it from QA.

CREATE TABLE #myTest
( InvNo int )

INSERT #myTest
select invoice_no from scheme.opdtcosm
where invoice_no not in (select journal_no from scheme.nljaudm)

SELECT * FROM #myTest

DROP TABLE #myTest

This will output the contents of the temp table to QA, and you should then be looking for duplicates.

Logicalman