Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

CURSOR question

3 posts in General Discussion Last posting was on 2004-06-22 19:34:55.0Z
Edgard Riba Posted on 2004-05-11 22:37:31.0Z
From: "Edgard Riba" <el_PLEASE_DONT_SPAM_ME_riba@rimith.com>
Newsgroups: ianywhere.public.general
Subject: CURSOR question
Lines: 144
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: 200.75.228.213
Message-ID: <40a155a7$1@forums-2-dub>
X-Original-Trace: 11 May 2004 15:37:27 -0700, 200.75.228.213
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 11 May 2004 15:37:28 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 11 May 2004 15:37:31 -0700
X-Trace: forums-1-dub 1084315051 10.22.108.75 (11 May 2004 15:37:31 -0700)
X-Original-Trace: 11 May 2004 15:37:31 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3043
Article PK: 6509

Hi,
I'm trying to use the DECLARE cursor syntax to try to develop stored
procedures compatible with MSSQL, but I'm running into problems.

Here is a stored procedure I'm working on. It fails saying that I'm
trying to insert a record into hFacts where ITEM is null. However, I have
an explicit filter for items being null in the select statement used for the
cursor.

Could someone give me a hand here, I can't figure this out.....
Thanks,Edgard

CREATE PROCEDURE hRegeneraFactsMensual (IN pBodega INT,IN pFechaINI DATE,IN
pFechaFIN DATE)
BEGIN ATOMIC
DECLARE HFacts1Cursor
NO SCROLL
CURSOR FOR SELECT
iTranR.Item AS xxItem,
ISNULL(iTranR.Cantidad,0) AS xxCantidad,
ISNULL(iTranR.Costo,0) AS xxCosto,
ISNULL(iTranR.Precio,0) AS xxPrecio,
iTran.Fecha AS xxFecha,
IF ISNULL(iTran.ClienteSuplidor,0)=0 THEN pBodega ELSE
iTran.ClienteSuplidor ENDIF AS xxClienteSuplidor,
YMD(DATEPART(year,iTran.Fecha)+4000,MONTH(iTran.Fecha),1) AS
xxMes,
ISNULL(iCTr.Tipo,0) AS xxTipo,
ISNULL(iCTr.SaldoTipo,0) AS xxSaldoTipo
FROM iTrans AS iTran
JOIN iCodigoTrans AS iCTr ON iCTr.Bodega=iTran.Bodega AND
iCTr.CodigoTrans=iTran.CodigoTrans
JOIN iTransRow AS iTranR ON iTran.idLoc=iTranR.idLoc AND
iTran.idSeq=iTranR.idSeq
WHERE iTran.Fecha BETWEEN gFechaINI AND gFechaFIN AND
iTran.Bodega=pBodega
AND iTran.Status&64<>0 AND iTran.Status&16=0
AND iTranR.Item IS NOT NULL
AND iCTr.Inv <= 1
FOR READ ONLY;
// Vars...
DECLARE xItem INT;
DECLARE xCantidad DECIMAL(11,5);
DECLARE xCosto DECIMAL(8,5);
DECLARE xPrecio DECIMAL(8,5);
DECLARE xFecha DATE;
DECLARE xClienteSuplidor INT;
DECLARE xMes DATE;
DECLARE xTipo INT;
DECLARE xSaldoTipo INT;
//
//----------------------------------------------------------------------
DECLARE gFechaINI DATE;
DECLARE gFechaFIN DATE;
//
DECLARE gMesINI DATE; //Estos los usamos para borrar
DECLARE gMesFIN DATE;
//
DECLARE gFecha DATE;
DECLARE gCounter INT;
SET TEMPORARY OPTION background_priority='on';
SET gCounter = 0;
//
SET gFechaINI = YMD(DATEPART(year,pFechaINI),MONTH(pFechaINI),1);
SET gFechaFIN = DATEADD(day,-DAY(pFechaFIN)+1,pFechaFIN);
SET gFechaFIN = DATEADD(day,-1,DATEADD(month,1,gFechaFIN));
//
//Calculamos los meses correspondientes a la fecha inicial y la final
para borrar...
SET gMesINI = YMD(DATEPART(year,gFechaINI)+4000,MONTH(gFechaINI),1);
SET gMesFIN = YMD(DATEPART(year,gFechaFIN)+4000,MONTH(gFechaFIN),1);
//
//
MESSAGE 'ACTUALIZACION FACT TABLE DE ' || pBodega || ' ENTRE ' ||
gFechaINI || ' Y ' || gFechaFIN;
MESSAGE '--->Empezó: ' || CURRENT TIMESTAMP;
//
//Limpiamos los datos...
//----------------------------------------------------------------------
UPDATE hFacts
SET Cantidad = 0,
CostoTotal = 0,
ValorTotal = 0
WHERE Bodega=pBodega AND Fecha BETWEEN gMesINI AND gMesFIN
;
MESSAGE '--->BORRAMOS DATOS VIEJOS - ' || CURRENT TIMESTAMP;
//----------------------------------------------------------------------
//Calculamos el movimiento...
OPEN HFacts1Cursor;
LOOP
FETCH NEXT HFacts1Cursor INTO
xItem,
xCantidad,
xCosto,
xPrecio,
xFecha,
xClienteSuplidor,
xMes,
xTipo,
xSaldoTipo
;
//
//
SET gCounter = gCounter + 1;
IF MOD(gCounter,500)=0 THEN
MESSAGE '....HEMOS PROCESADO:' || gCounter ;
END IF;
//
///
//MENSUAL
SET gFecha = xMes;
IF NOT EXISTS(SELECT item FROM hFacts WHERE item=xitem AND
tipo=xTipo AND Fecha=gFecha AND Bodega=pBodega AND
ClienteSuplidor=xClienteSuplidor) THEN
INSERT INTO
hFacts(Item,Tipo,Fecha,Bodega,ClienteSuplidor,Cantidad,CostoTotal,ValorTotal
)

VALUES(xItem,xTipo,gFecha,pBodega,xClienteSuplidor,xCantidad*xSaldoTipo,xCan
tidad*xCosto*xSaldoTipo,xCantidad*xPrecio*xSaldoTipo);
ELSE
UPDATE hFacts
SET Cantidad = Cantidad + (xCantidad *
xSaldoTipo ),
CostoTotal = CostoTotal + (xCantidad * xCosto *
xSaldoTipo ),
ValorTotal = ValorTotal + (xCantidad * xPrecio *
xSaldoTipo)
WHERE Item=xItem AND tipo=xTipo AND Fecha=gFecha AND
Bodega=pBodega AND ClienteSuplidor=xClienteSuplidor;
END IF;
END LOOP;
CLOSE HFacts1Cursor;
//----------------------------------------------------------------------
//LISTO....
MESSAGE '--->Finalizó - ' || gCounter || ' registros procesados -' ||
CURRENT TIMESTAMP;
MESSAGE 'FIN hFactsMensual - ' || pBodega || ' ENTRE ' || gFechaINI || '
Y ' || gFechaFIN;
CALL AddEvent(STRING('hRegeneraFacts - ',pBodega,' ENTRE ',gFechaINI,' Y
',gFechaFIN));
END;


Edgard Riba Posted on 2004-05-11 22:41:44.0Z
From: "Edgard Riba" <el_PLEASE_DONT_SPAM_ME_riba@rimith.com>
Newsgroups: ianywhere.public.general
References: <40a155a7$1@forums-2-dub>
Subject: Re: CURSOR question
Lines: 3
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
X-Original-NNTP-Posting-Host: 200.75.228.213
Message-ID: <40a156a4@forums-2-dub>
X-Original-Trace: 11 May 2004 15:41:40 -0700, 200.75.228.213
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 11 May 2004 15:41:42 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 11 May 2004 15:41:44 -0700
X-Trace: forums-1-dub 1084315304 10.22.108.75 (11 May 2004 15:41:44 -0700)
X-Original-Trace: 11 May 2004 15:41:44 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3044
Article PK: 6508

Sorry, wrong place....


Ani Nica Posted on 2004-06-22 19:34:55.0Z
From: "Ani Nica" <ani@sybase>
Newsgroups: ianywhere.public.general
References: <40a155a7$1@forums-2-dub>
Subject: Re: CURSOR question
Lines: 165
Organization: iAnywhere Solutions
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Original-NNTP-Posting-Host: anica-pc.sybase.com
Message-ID: <40d888dc$1@forums-2-dub>
X-Original-Trace: 22 Jun 2004 12:30:36 -0700, anica-pc.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 22 Jun 2004 12:25:21 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 22 Jun 2004 12:34:55 -0700
X-Trace: forums-1-dub 1087932895 10.22.108.75 (22 Jun 2004 12:34:55 -0700)
X-Original-Trace: 22 Jun 2004 12:34:55 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:3198
Article PK: 6655

Could you please post the definition of the table hFacts?

Thanks
Ani Nica [iAnywhere Solutions]

"Edgard Riba" <el_PLEASE_DONT_SPAM_ME_riba@rimith.com> wrote in message
news:40a155a7$1@forums-2-dub...
> Hi,
> I'm trying to use the DECLARE cursor syntax to try to develop stored
> procedures compatible with MSSQL, but I'm running into problems.
>
> Here is a stored procedure I'm working on. It fails saying that I'm
> trying to insert a record into hFacts where ITEM is null. However, I
have
> an explicit filter for items being null in the select statement used for
the
> cursor.
>
> Could someone give me a hand here, I can't figure this out.....
> Thanks,Edgard
>
> CREATE PROCEDURE hRegeneraFactsMensual (IN pBodega INT,IN pFechaINI
DATE,IN
> pFechaFIN DATE)
> BEGIN ATOMIC
> DECLARE HFacts1Cursor
> NO SCROLL
> CURSOR FOR SELECT
> iTranR.Item AS xxItem,
> ISNULL(iTranR.Cantidad,0) AS xxCantidad,
> ISNULL(iTranR.Costo,0) AS xxCosto,
> ISNULL(iTranR.Precio,0) AS xxPrecio,
> iTran.Fecha AS xxFecha,
> IF ISNULL(iTran.ClienteSuplidor,0)=0 THEN pBodega ELSE
> iTran.ClienteSuplidor ENDIF AS xxClienteSuplidor,
> YMD(DATEPART(year,iTran.Fecha)+4000,MONTH(iTran.Fecha),1) AS
> xxMes,
> ISNULL(iCTr.Tipo,0) AS xxTipo,
> ISNULL(iCTr.SaldoTipo,0) AS xxSaldoTipo
> FROM iTrans AS iTran
> JOIN iCodigoTrans AS iCTr ON iCTr.Bodega=iTran.Bodega AND
> iCTr.CodigoTrans=iTran.CodigoTrans
> JOIN iTransRow AS iTranR ON iTran.idLoc=iTranR.idLoc AND
> iTran.idSeq=iTranR.idSeq
> WHERE iTran.Fecha BETWEEN gFechaINI AND gFechaFIN AND
> iTran.Bodega=pBodega
> AND iTran.Status&64<>0 AND iTran.Status&16=0
> AND iTranR.Item IS NOT NULL
> AND iCTr.Inv <= 1
> FOR READ ONLY;
> // Vars...
> DECLARE xItem INT;
> DECLARE xCantidad DECIMAL(11,5);
> DECLARE xCosto DECIMAL(8,5);
> DECLARE xPrecio DECIMAL(8,5);
> DECLARE xFecha DATE;
> DECLARE xClienteSuplidor INT;
> DECLARE xMes DATE;
> DECLARE xTipo INT;
> DECLARE xSaldoTipo INT;
> //
>
//----------------------------------------------------------------------
> DECLARE gFechaINI DATE;
> DECLARE gFechaFIN DATE;
> //
> DECLARE gMesINI DATE; //Estos los usamos para borrar
> DECLARE gMesFIN DATE;
> //
> DECLARE gFecha DATE;
> DECLARE gCounter INT;
> SET TEMPORARY OPTION background_priority='on';
> SET gCounter = 0;
> //
> SET gFechaINI = YMD(DATEPART(year,pFechaINI),MONTH(pFechaINI),1);
> SET gFechaFIN = DATEADD(day,-DAY(pFechaFIN)+1,pFechaFIN);
> SET gFechaFIN = DATEADD(day,-1,DATEADD(month,1,gFechaFIN));
> //
> //Calculamos los meses correspondientes a la fecha inicial y la final
> para borrar...
> SET gMesINI = YMD(DATEPART(year,gFechaINI)+4000,MONTH(gFechaINI),1);
> SET gMesFIN = YMD(DATEPART(year,gFechaFIN)+4000,MONTH(gFechaFIN),1);
> //
> //
> MESSAGE 'ACTUALIZACION FACT TABLE DE ' || pBodega || ' ENTRE ' ||
> gFechaINI || ' Y ' || gFechaFIN;
> MESSAGE '--->Empezó: ' || CURRENT TIMESTAMP;
> //
> //Limpiamos los datos...
>
//----------------------------------------------------------------------
> UPDATE hFacts
> SET Cantidad = 0,
> CostoTotal = 0,
> ValorTotal = 0
> WHERE Bodega=pBodega AND Fecha BETWEEN gMesINI AND gMesFIN
> ;
> MESSAGE '--->BORRAMOS DATOS VIEJOS - ' || CURRENT TIMESTAMP;
>
//----------------------------------------------------------------------
> //Calculamos el movimiento...
> OPEN HFacts1Cursor;
> LOOP
> FETCH NEXT HFacts1Cursor INTO
> xItem,
> xCantidad,
> xCosto,
> xPrecio,
> xFecha,
> xClienteSuplidor,
> xMes,
> xTipo,
> xSaldoTipo
> ;
> //
> //
> SET gCounter = gCounter + 1;
> IF MOD(gCounter,500)=0 THEN
> MESSAGE '....HEMOS PROCESADO:' || gCounter ;
> END IF;
> //
> ///
> //MENSUAL
> SET gFecha = xMes;
> IF NOT EXISTS(SELECT item FROM hFacts WHERE item=xitem AND
> tipo=xTipo AND Fecha=gFecha AND Bodega=pBodega AND
> ClienteSuplidor=xClienteSuplidor) THEN
> INSERT INTO
>
hFacts(Item,Tipo,Fecha,Bodega,ClienteSuplidor,Cantidad,CostoTotal,ValorTotal
> )
>
>
VALUES(xItem,xTipo,gFecha,pBodega,xClienteSuplidor,xCantidad*xSaldoTipo,xCan
> tidad*xCosto*xSaldoTipo,xCantidad*xPrecio*xSaldoTipo);
> ELSE
> UPDATE hFacts
> SET Cantidad = Cantidad + (xCantidad *
> xSaldoTipo ),
> CostoTotal = CostoTotal + (xCantidad * xCosto *
> xSaldoTipo ),
> ValorTotal = ValorTotal + (xCantidad * xPrecio *
> xSaldoTipo)
> WHERE Item=xItem AND tipo=xTipo AND Fecha=gFecha AND
> Bodega=pBodega AND ClienteSuplidor=xClienteSuplidor;
> END IF;
> END LOOP;
> CLOSE HFacts1Cursor;
>
//----------------------------------------------------------------------
> //LISTO....
> MESSAGE '--->Finalizó - ' || gCounter || ' registros procesados -' ||
> CURRENT TIMESTAMP;
> MESSAGE 'FIN hFactsMensual - ' || pBodega || ' ENTRE ' || gFechaINI ||
'
> Y ' || gFechaFIN;
> CALL AddEvent(STRING('hRegeneraFacts - ',pBodega,' ENTRE ',gFechaINI,'
Y
> ',gFechaFIN));
> END;
>
>