can't insert into table using a cursor

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

can't insert into table using a cursor

Author

Topic

tortoise
Starting Member

2 Posts

Posted - 2011-03-02 : 05:41:18

please help. i have written the following code but it is not inserting the rows please help me find a way to insert these records

BEGIN
DECLARE
CURSOR C IS
SELECT (SUM(FLAG||AMOUNT)/12) SUM1 ,SHORT_NAME TRANS_DESCRIPTION,LEDGER_ACC
from gen_ledger,LEDGER_MAINT
where ledger_acc between 5000 and 8999
AND CODE = LEDGER_ACC
and amount is not null
GROUP BY
SHORT_NAME,LEDGER_ACC ;

BEGIN

SELECT seq_journal.NEXTVAL INTO SEQ FROM DUAL;
REFERENCEN_NO1 := SEQ;

for I IN C
LOOP

IF SUBSTR(SUM1,1,1) = '-' THEN
AMT2 := SUBSTR(SUM1,2);
FLA1 := '-';

ELSE
AMT2 := SUM1;
FLA1 := '+';
END IF;

INSERT INTO GEN_LEDGER(AMOUNT,TRANS_DESCRIPTION,trans_code,gen_ledger.created_by,
gen_ledger.created_date,flag,effective_date,LEDGER_ACC ,referencen_no)

VALUES (AMT2,I.TRANS_DESCRIPTION,'JV',USER,SYSDATE,FLA1,:EFFECTIVEDATE,I.LEDGER_ACC,REFERENCEN_NO1);

END LOOP;

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-02 : 05:49:39

in which DBMS are you working in ?

Cheers
MIK