Re-2: Declaring Cursor inside a loop

[Fabian Eschrich]

Hi Ivan,

I reported it.
So for this time I simply splitted it in a procedure and a function. But there I have a new problem:

DECLARE vCustomer_id VARCHAR(255);
	DECLARE vTdate DATE;
	DECLARE vNet double;
		
        DECLARE vcurTransactions CURSOR FOR select getdate(TRANSACTIONS.tdate),TRANSACTIONS.NET,TRANSACTIONS.customer_id from transactions;
                              
        OPEN vcurTransactions;

        BEGIN
            FETCH FIRST vcurTransactions INTO vTdate,vNet,vCustomer_id;
			CALL SaveStatisticDoubleOnDateForAddress(vCustomer_id,vTdate,'transaction',vNet);



-- this is my declaration for function SaveStatisticsDoubleOnDateForAddress
CREATE OR REPLACE PROCEDURE [SaveStatisticDoubleOnDateForAddress]( IN VADDRESSID VARCHAR, IN VDATE DATE, IN VNAME VARCHAR, IN VVAL DOUBLE )


But the log says:  18:40:37 Kernel error: 0x62501. Date value "2006-06-29" is invalid. Illegal symbol at "3" position.

-------- Original Message --------
Subject: Re: Declaring Cursor inside a loop (20-Aug-2008 15:04)
From:    Ivan Smahin <ivan_smahin at paradigmasoft.com>
To:      valentina at lists.macserve.net

> Hello Fabian,
> 
> Wednesday, August 20, 2008, 2:26:01 PM, you wrote:
> 
> > Hi,
> 
> > I am working on my first procedure.
> 
> > Inside a loop I have to check if a table already has a record for a
> > specific date, so I redeclare a cursor with every loop.
> > But valentina tells me 
> 
> >  13:25:28 Kernel error: 0x56501. Cursor "vcurDatesum2" is opened already.
> 
> > This is my code inside the loop.
> 
> > LOOP
> >                                 BEGIN
> >                                         print 'Begin Loop';
> >                                         FETCH NEXT vcurTransactions INTO 
> > vTdate,vNet,vCustomer_id;
> >                                         BEGIN
> >                                                 DECLARE
> > vcurDatesum2 CURSOR FOR select VAL1 from statistics where val1 =
> > datetrunc(vTdate,'day') AND addressid = vCustomer_id;
> >                                                 DECLARE vVal1 VARCHAR(255);
> >                                                 
> >                                                 OPEN vcurDatesum2;
> >                                                 FETCH FIRST vcurDatesum2 
> > INTO vVal1;
> >                                                 close vcurDatesum2;
> >                                                 Print 'Fetched first 
> > Datesum';
> >                                                 update statistics
> > set val2 = val2 + vNet where val1 = datetrunc(vTdate,'day') AND addressid = 
> > vCustomer_id;
> >                                         EXCEPTION WHEN ERR_CURSOR_WRONG_
> > POSITION THEN
> >                                                 PRINT 'Exception in Loop 
> > with Datesum2';
> >                                                 INSERT INTO
> > statistics (addressid,val1,val2) VALUES
> > (vCustomer_id,datetrunc(vTdate,'day'),vNet);
> >                                                 
> >                                         End
> >                                         
> >                 
> >                                 EXCEPTION WHEN ERR_CURSOR_WRONG_POSITION 
> > THEN
> >                                         LEAVE
> >                                 END
> 
> >                         END LOOP
> 
> > What can I do?
> 
> Seems to be a bug. Report it to Mantis please
> 
> -- 
> Best regards,
> Ivan Smahin 
> Senior Software Engineer
> Paradigma Software, Inc
> Valentina - The Ultra-Fast Database
> http://www.valentina-db.com
> 
> _______________________________________________
> Valentina mailing list
> Valentina at lists.macserve.net
> http://lists.macserve.net/mailman/listinfo/valentina
>