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.

Declare cursor end of procedure giving ERROR.

5 posts in General Discussion Last posting was on 2004-02-27 16:22:25.0Z
Chandresh Patel Posted on 2004-02-27 01:19:55.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
Subject: Declare cursor end of procedure giving ERROR.
Lines: 116
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: ac84a811.ipt.aol.com
X-Original-NNTP-Posting-Host: ac84a811.ipt.aol.com
Message-ID: <403e9b3b@forums-1-dub>
Date: 26 Feb 2004 17:19:55 -0800
X-Trace: forums-1-dub 1077844795 172.132.168.17 (26 Feb 2004 17:19:55 -0800)
X-Original-Trace: 26 Feb 2004 17:19:55 -0800, ac84a811.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2667
Article PK: 6141

Sybase Anywhere 9.0.0.1108

In my procedure i declare one cursor(crsr) at the starting and at the end of
procedure I declare another cursor (crsr1)
in WHILE loop but at the time of saving procedure it giveing me error like:

[Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
violation: near 'declare' in ... [declare] crsr1 cursor for...
SQLCODE: -131
SQLSTATE: 42000


Here is my procedure
===============

ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
BEGIN
declare EOF_found exception for sqlstate value '02000';
declare mStoreName varchar(40);
declare mFieldData long varchar;
DECLARE StartDate DATE;
DECLARE EndDate DATE;

declare crsr cursor for call GetStoreName(mSGroupID);
open crsr;

SET mFieldData = 'DisplayDates VARCHAR(30)';

MyLoop: loop
fetch next crsr into mStoreName;
if sqlstate = EOF_found then
leave MyLoop;
else
SET mStoreName = REPLACE(mStoreName, ' ', '');
SET mStoreName = REPLACE(mStoreName, '*', '');
if mFieldData IS NULL THEN
SET mFieldData = STRING (mStoreName,' FLOAT');
else
SET mFieldData = STRING (mFieldData,', ',mStoreName,'
FLOAT');
end if;
end if;
end loop MyLoop;

close crsr;

SET mFieldData = STRING ('DECLARE LOCAL TEMPORARY TABLE SalesData(',
mFieldData, ' ) NOT TRANSACTIONAL');
execute immediate mFieldData;

//Get all sales Data.
SET StartDate = sDate;
SET EndDate = eDate;

//Check Report Type
IF ReportType = 0 THEN
CALL GetCurrentWeekDates(StartDate, EndDate);
ELSEIF ReportType = 1 THEN
CALL GetCurrentYearDates(StartDate, EndDate);
ELSEIF ReportType = 2 THEN
CALL GetCurrentQuarterDates(StartDate, EndDate);
ELSEIF ReportType = 3 THEN
CALL GetCurrentMonthDates(StartDate, EndDate);
ELSEIF ReportType = 4 THEN
CALL GetCurrentDay(StartDate, EndDate);
END IF;

//Get sum of NetSales, SalesTax and CustomerCount
WHILE sDate <= eDate LOOP
declare crsr1 cursor for select ifnull(sum(A.DunkinSales), 0,
sum(A.DunkinSales)) - (ifnull(sum(A.Overring), 0 , sum(A.Overring)) +
ifnull(sum(A.SalesTax), 0, sum(A.SalesTax))) as NetSales from Sales A,
DayActivity B where A.DayActivityID = B.DayActivityID and B.StoreID IN
(select StoreID FROM StoreGroup WHERE StoreGroupID=mSGroupID) and
CAST(DayDate AS DATE) BETWEEN StartDate and EndDate GROUP BY B.StoreID;

open crsr1;

SET AllSales = '';

MyLoop: loop
fetch next crsr1 into mFValue;
if sqlstate = EOF_found then
leave MyLoop;
else
if mFValue IS NULL THEN
SET mFValue = STRING (mFValue);
else
SET mFValue = STRING (mFValue,', ',mFValue);
end if;
end if;
end loop MyLoop;

close crsr1;

//Check Report Type
IF ReportType = 0 THEN
CALL GetPreviousWeekDates(StartDate, EndDate);
ELSEIF ReportType = 1 THEN
CALL GetPreviousYearDates(StartDate, EndDate);
ELSEIF ReportType = 2 THEN
CALL GetPreviousQuarterDates(StartDate, EndDate);
ELSEIF ReportType = 3 THEN
CALL GetPreviousMonthDates(StartDate, EndDate);
ELSEIF ReportType = 4 THEN
CALL GetPreviousDay(StartDate, EndDate);
END IF;

SET eDate = EndDate;
END LOOP;

SELECT * FROM SalesData;
END


Chris Keating (iAnywhere Solutions) Posted on 2004-02-27 01:42:19.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403e9b3b@forums-1-dub>
Subject: Re: Declare cursor end of procedure giving ERROR.
Lines: 151
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: keating-xp.sybase.com
X-Original-NNTP-Posting-Host: keating-xp.sybase.com
Message-ID: <403ea07b@forums-1-dub>
Date: 26 Feb 2004 17:42:19 -0800
X-Trace: forums-1-dub 1077846139 172.31.141.1 (26 Feb 2004 17:42:19 -0800)
X-Original-Trace: 26 Feb 2004 17:42:19 -0800, keating-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2668
Article PK: 6143

DECLAREs must occur at the start of a compound statement.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403e9b3b@forums-1-dub...
> Sybase Anywhere 9.0.0.1108
>
> In my procedure i declare one cursor(crsr) at the starting and at the end
of
> procedure I declare another cursor (crsr1)
> in WHILE loop but at the time of saving procedure it giveing me error
like:
>
> [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> violation: near 'declare' in ... [declare] crsr1 cursor for...
> SQLCODE: -131
> SQLSTATE: 42000
>
>
> Here is my procedure
> ===============
>
> ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> BEGIN
> declare EOF_found exception for sqlstate value '02000';
> declare mStoreName varchar(40);
> declare mFieldData long varchar;
> DECLARE StartDate DATE;
> DECLARE EndDate DATE;
>
> declare crsr cursor for call GetStoreName(mSGroupID);
> open crsr;
>
> SET mFieldData = 'DisplayDates VARCHAR(30)';
>
> MyLoop: loop
> fetch next crsr into mStoreName;
> if sqlstate = EOF_found then
> leave MyLoop;
> else
> SET mStoreName = REPLACE(mStoreName, ' ', '');
> SET mStoreName = REPLACE(mStoreName, '*', '');
> if mFieldData IS NULL THEN
> SET mFieldData = STRING (mStoreName,' FLOAT');
> else
> SET mFieldData = STRING (mFieldData,', ',mStoreName,'
> FLOAT');
> end if;
> end if;
> end loop MyLoop;
>
> close crsr;
>
> SET mFieldData = STRING ('DECLARE LOCAL TEMPORARY TABLE SalesData(',
> mFieldData, ' ) NOT TRANSACTIONAL');
> execute immediate mFieldData;
>
> //Get all sales Data.
> SET StartDate = sDate;
> SET EndDate = eDate;
>
> //Check Report Type
> IF ReportType = 0 THEN
> CALL GetCurrentWeekDates(StartDate, EndDate);
> ELSEIF ReportType = 1 THEN
> CALL GetCurrentYearDates(StartDate, EndDate);
> ELSEIF ReportType = 2 THEN
> CALL GetCurrentQuarterDates(StartDate, EndDate);
> ELSEIF ReportType = 3 THEN
> CALL GetCurrentMonthDates(StartDate, EndDate);
> ELSEIF ReportType = 4 THEN
> CALL GetCurrentDay(StartDate, EndDate);
> END IF;
>
> //Get sum of NetSales, SalesTax and CustomerCount
> WHILE sDate <= eDate LOOP
> declare crsr1 cursor for select ifnull(sum(A.DunkinSales), 0,
> sum(A.DunkinSales)) - (ifnull(sum(A.Overring), 0 , sum(A.Overring)) +
> ifnull(sum(A.SalesTax), 0, sum(A.SalesTax))) as NetSales from Sales A,
> DayActivity B where A.DayActivityID = B.DayActivityID and B.StoreID IN
> (select StoreID FROM StoreGroup WHERE StoreGroupID=mSGroupID) and
> CAST(DayDate AS DATE) BETWEEN StartDate and EndDate GROUP BY B.StoreID;
>
> open crsr1;
>
> SET AllSales = '';
>
> MyLoop: loop
> fetch next crsr1 into mFValue;
> if sqlstate = EOF_found then
> leave MyLoop;
> else
> if mFValue IS NULL THEN
> SET mFValue = STRING (mFValue);
> else
> SET mFValue = STRING (mFValue,', ',mFValue);
> end if;
> end if;
> end loop MyLoop;
>
> close crsr1;
>
> //Check Report Type
> IF ReportType = 0 THEN
> CALL GetPreviousWeekDates(StartDate, EndDate);
> ELSEIF ReportType = 1 THEN
> CALL GetPreviousYearDates(StartDate, EndDate);
> ELSEIF ReportType = 2 THEN
> CALL GetPreviousQuarterDates(StartDate, EndDate);
> ELSEIF ReportType = 3 THEN
> CALL GetPreviousMonthDates(StartDate, EndDate);
> ELSEIF ReportType = 4 THEN
> CALL GetPreviousDay(StartDate, EndDate);
> END IF;
>
> SET eDate = EndDate;
> END LOOP;
>
> SELECT * FROM SalesData;
> END
>
>


Chandresh Patel Posted on 2004-02-27 04:22:21.0Z
From: "Chandresh Patel" <bapashree@yahoo.com>
Newsgroups: ianywhere.public.general
References: <403e9b3b@forums-1-dub> <403ea07b@forums-1-dub>
Subject: Re: Declare cursor end of procedure giving ERROR.
Lines: 170
Organization: Shreeji Smart Solution
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: ac84a811.ipt.aol.com
Message-ID: <403ec612@forums-2-dub>
X-Original-Trace: 26 Feb 2004 20:22:42 -0800, ac84a811.ipt.aol.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Feb 2004 20:21:35 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Feb 2004 20:22:21 -0800
X-Trace: forums-1-dub 1077855741 10.22.108.75 (26 Feb 2004 20:22:21 -0800)
X-Original-Trace: 26 Feb 2004 20:22:21 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2670
Article PK: 6142

Did you see my situation in procedure. I need cursor in while loop because
each loop I get StartDate and EndDate and based on that create query and
fetch it in to cursor.

Is there any way or idea to declare at start and use in while loop for
number of times.

Thanks in Advance.



"Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>

wrote in message news:403ea07b@forums-1-dub...
> DECLAREs must occur at the start of a compound statement.
>
> --
>
> Chris Keating
> Sybase Adaptive Server Anywhere Professional Version 8
>
>
****************************************************************************
> *
> Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
> and try out the market-leading database for mobile, embedded and small to
> medium sized business environments for free!
>
> http://www.ianywhere.com/promos/deved/index.html
>
>
****************************************************************************
> *
>
> iAnywhere Solutions http://www.iAnywhere.com
>
> ** Please only post to the newsgroup
>
> ** Whitepapers can be found at http://www.iAnywhere.com/developer
> ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> ** Use CaseXpress to report bugs http://casexpress.sybase.com
>
>
****************************************************************************
> *
>
> "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> news:403e9b3b@forums-1-dub...
> > Sybase Anywhere 9.0.0.1108
> >
> > In my procedure i declare one cursor(crsr) at the starting and at the
end
> of
> > procedure I declare another cursor (crsr1)
> > in WHILE loop but at the time of saving procedure it giveing me error
> like:
> >
> > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> > violation: near 'declare' in ... [declare] crsr1 cursor for...
> > SQLCODE: -131
> > SQLSTATE: 42000
> >
> >
> > Here is my procedure
> > ===============
> >
> > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > BEGIN
> > declare EOF_found exception for sqlstate value '02000';
> > declare mStoreName varchar(40);
> > declare mFieldData long varchar;
> > DECLARE StartDate DATE;
> > DECLARE EndDate DATE;
> >
> > declare crsr cursor for call GetStoreName(mSGroupID);
> > open crsr;
> >
> > SET mFieldData = 'DisplayDates VARCHAR(30)';
> >
> > MyLoop: loop
> > fetch next crsr into mStoreName;
> > if sqlstate = EOF_found then
> > leave MyLoop;
> > else
> > SET mStoreName = REPLACE(mStoreName, ' ', '');
> > SET mStoreName = REPLACE(mStoreName, '*', '');
> > if mFieldData IS NULL THEN
> > SET mFieldData = STRING (mStoreName,' FLOAT');
> > else
> > SET mFieldData = STRING (mFieldData,', ',mStoreName,'
> > FLOAT');
> > end if;
> > end if;
> > end loop MyLoop;
> >
> > close crsr;
> >
> > SET mFieldData = STRING ('DECLARE LOCAL TEMPORARY TABLE SalesData(',
> > mFieldData, ' ) NOT TRANSACTIONAL');
> > execute immediate mFieldData;
> >
> > //Get all sales Data.
> > SET StartDate = sDate;
> > SET EndDate = eDate;
> >
> > //Check Report Type
> > IF ReportType = 0 THEN
> > CALL GetCurrentWeekDates(StartDate, EndDate);
> > ELSEIF ReportType = 1 THEN
> > CALL GetCurrentYearDates(StartDate, EndDate);
> > ELSEIF ReportType = 2 THEN
> > CALL GetCurrentQuarterDates(StartDate, EndDate);
> > ELSEIF ReportType = 3 THEN
> > CALL GetCurrentMonthDates(StartDate, EndDate);
> > ELSEIF ReportType = 4 THEN
> > CALL GetCurrentDay(StartDate, EndDate);
> > END IF;
> >
> > //Get sum of NetSales, SalesTax and CustomerCount
> > WHILE sDate <= eDate LOOP
> > declare crsr1 cursor for select ifnull(sum(A.DunkinSales), 0,
> > sum(A.DunkinSales)) - (ifnull(sum(A.Overring), 0 , sum(A.Overring)) +
> > ifnull(sum(A.SalesTax), 0, sum(A.SalesTax))) as NetSales from Sales A,
> > DayActivity B where A.DayActivityID = B.DayActivityID and B.StoreID IN
> > (select StoreID FROM StoreGroup WHERE StoreGroupID=mSGroupID) and
> > CAST(DayDate AS DATE) BETWEEN StartDate and EndDate GROUP BY B.StoreID;
> >
> > open crsr1;
> >
> > SET AllSales = '';
> >
> > MyLoop: loop
> > fetch next crsr1 into mFValue;
> > if sqlstate = EOF_found then
> > leave MyLoop;
> > else
> > if mFValue IS NULL THEN
> > SET mFValue = STRING (mFValue);
> > else
> > SET mFValue = STRING (mFValue,', ',mFValue);
> > end if;
> > end if;
> > end loop MyLoop;
> >
> > close crsr1;
> >
> > //Check Report Type
> > IF ReportType = 0 THEN
> > CALL GetPreviousWeekDates(StartDate, EndDate);
> > ELSEIF ReportType = 1 THEN
> > CALL GetPreviousYearDates(StartDate, EndDate);
> > ELSEIF ReportType = 2 THEN
> > CALL GetPreviousQuarterDates(StartDate, EndDate);
> > ELSEIF ReportType = 3 THEN
> > CALL GetPreviousMonthDates(StartDate, EndDate);
> > ELSEIF ReportType = 4 THEN
> > CALL GetPreviousDay(StartDate, EndDate);
> > END IF;
> >
> > SET eDate = EndDate;
> > END LOOP;
> >
> > SELECT * FROM SalesData;
> > END
> >
> >
>
>


Reg Domaratzki Posted on 2004-02-27 16:22:25.0Z
From: "Reg Domaratzki" <Spam_bad_rdomarat@ianywhere.com>
Newsgroups: ianywhere.public.general
References: <403e9b3b@forums-1-dub> <403ea07b@forums-1-dub> <403ec612@forums-2-dub>
Subject: Re: Declare cursor end of procedure giving ERROR.
Lines: 239
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: rdomarat-pc.sybase.com
X-Original-NNTP-Posting-Host: rdomarat-pc.sybase.com
Message-ID: <403f6ec1@forums-1-dub>
Date: 27 Feb 2004 08:22:25 -0800
X-Trace: forums-1-dub 1077898945 172.31.143.163 (27 Feb 2004 08:22:25 -0800)
X-Original-Trace: 27 Feb 2004 08:22:25 -0800, rdomarat-pc.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2672
Article PK: 6145

ASA SQL Reference
SQL Statements
DECLARE CURSOR statement [ESQL] [SP]

[snip]

USING variable-name
For use within stored procedures only. The variable is a string containing a
SELECT statement for the cursor. The variable must be available when the
DECLARE is processed, and so must be one of the following:
- A parameter to the procedure. For example,

create function get_row_count(in qry long varchar)
returns int
begin
declare crsr cursor using qry;
declare rowcnt int;

set rowcnt = 0;
open crsr;
lp: loop
fetch crsr;
if SQLCODE <> 0 then leave lp end if;
set rowcnt = rowcnt + 1;
end loop;
return rowcnt;
end

- Nested inside another BEGIN... END after the variable has been assigned a
value. For example,

create procedure get_table_name(
in id_value int, out tabname char(128)
)
begin
declare qry long varchar;

set qry = 'select table_name from SYS.SYSTABLE ' ||
'where table_id=' || string(id_value);
begin
declare crsr cursor using qry;

open crsr;
fetch crsr into tabname;
close crsr;
end
end

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403ec612@forums-2-dub...
> Did you see my situation in procedure. I need cursor in while loop because
> each loop I get StartDate and EndDate and based on that create query and
> fetch it in to cursor.
>
> Is there any way or idea to declare at start and use in while loop for
> number of times.
>
> Thanks in Advance.
>
>
>
> "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> wrote in message news:403ea07b@forums-1-dub...
> > DECLAREs must occur at the start of a compound statement.
> >
> > --
> >
> > Chris Keating
> > Sybase Adaptive Server Anywhere Professional Version 8
> >
> >
>
****************************************************************************
> > *
> > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition
> > and try out the market-leading database for mobile, embedded and small
to
> > medium sized business environments for free!
> >
> > http://www.ianywhere.com/promos/deved/index.html
> >
> >
>
****************************************************************************
> > *
> >
> > iAnywhere Solutions http://www.iAnywhere.com
> >
> > ** Please only post to the newsgroup
> >
> > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> >
> >
>
****************************************************************************
> > *
> >
> > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > news:403e9b3b@forums-1-dub...
> > > Sybase Anywhere 9.0.0.1108
> > >
> > > In my procedure i declare one cursor(crsr) at the starting and at the
> end
> > of
> > > procedure I declare another cursor (crsr1)
> > > in WHILE loop but at the time of saving procedure it giveing me error
> > like:
> > >
> > > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> > > violation: near 'declare' in ... [declare] crsr1 cursor for...
> > > SQLCODE: -131
> > > SQLSTATE: 42000
> > >
> > >
> > > Here is my procedure
> > > ===============
> > >
> > > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > > BEGIN
> > > declare EOF_found exception for sqlstate value '02000';
> > > declare mStoreName varchar(40);
> > > declare mFieldData long varchar;
> > > DECLARE StartDate DATE;
> > > DECLARE EndDate DATE;
> > >
> > > declare crsr cursor for call GetStoreName(mSGroupID);
> > > open crsr;
> > >
> > > SET mFieldData = 'DisplayDates VARCHAR(30)';
> > >
> > > MyLoop: loop
> > > fetch next crsr into mStoreName;
> > > if sqlstate = EOF_found then
> > > leave MyLoop;
> > > else
> > > SET mStoreName = REPLACE(mStoreName, ' ', '');
> > > SET mStoreName = REPLACE(mStoreName, '*', '');
> > > if mFieldData IS NULL THEN
> > > SET mFieldData = STRING (mStoreName,' FLOAT');
> > > else
> > > SET mFieldData = STRING (mFieldData,', ',mStoreName,'
> > > FLOAT');
> > > end if;
> > > end if;
> > > end loop MyLoop;
> > >
> > > close crsr;
> > >
> > > SET mFieldData = STRING ('DECLARE LOCAL TEMPORARY TABLE
SalesData(',
> > > mFieldData, ' ) NOT TRANSACTIONAL');
> > > execute immediate mFieldData;
> > >
> > > //Get all sales Data.
> > > SET StartDate = sDate;
> > > SET EndDate = eDate;
> > >
> > > //Check Report Type
> > > IF ReportType = 0 THEN
> > > CALL GetCurrentWeekDates(StartDate, EndDate);
> > > ELSEIF ReportType = 1 THEN
> > > CALL GetCurrentYearDates(StartDate, EndDate);
> > > ELSEIF ReportType = 2 THEN
> > > CALL GetCurrentQuarterDates(StartDate, EndDate);
> > > ELSEIF ReportType = 3 THEN
> > > CALL GetCurrentMonthDates(StartDate, EndDate);
> > > ELSEIF ReportType = 4 THEN
> > > CALL GetCurrentDay(StartDate, EndDate);
> > > END IF;
> > >
> > > //Get sum of NetSales, SalesTax and CustomerCount
> > > WHILE sDate <= eDate LOOP
> > > declare crsr1 cursor for select ifnull(sum(A.DunkinSales), 0,
> > > sum(A.DunkinSales)) - (ifnull(sum(A.Overring), 0 , sum(A.Overring)) +
> > > ifnull(sum(A.SalesTax), 0, sum(A.SalesTax))) as NetSales from Sales A,
> > > DayActivity B where A.DayActivityID = B.DayActivityID and B.StoreID IN
> > > (select StoreID FROM StoreGroup WHERE StoreGroupID=mSGroupID) and
> > > CAST(DayDate AS DATE) BETWEEN StartDate and EndDate GROUP BY
B.StoreID;
> > >
> > > open crsr1;
> > >
> > > SET AllSales = '';
> > >
> > > MyLoop: loop
> > > fetch next crsr1 into mFValue;
> > > if sqlstate = EOF_found then
> > > leave MyLoop;
> > > else
> > > if mFValue IS NULL THEN
> > > SET mFValue = STRING (mFValue);
> > > else
> > > SET mFValue = STRING (mFValue,', ',mFValue);
> > > end if;
> > > end if;
> > > end loop MyLoop;
> > >
> > > close crsr1;
> > >
> > > //Check Report Type
> > > IF ReportType = 0 THEN
> > > CALL GetPreviousWeekDates(StartDate, EndDate);
> > > ELSEIF ReportType = 1 THEN
> > > CALL GetPreviousYearDates(StartDate, EndDate);
> > > ELSEIF ReportType = 2 THEN
> > > CALL GetPreviousQuarterDates(StartDate, EndDate);
> > > ELSEIF ReportType = 3 THEN
> > > CALL GetPreviousMonthDates(StartDate, EndDate);
> > > ELSEIF ReportType = 4 THEN
> > > CALL GetPreviousDay(StartDate, EndDate);
> > > END IF;
> > >
> > > SET eDate = EndDate;
> > > END LOOP;
> > >
> > > SELECT * FROM SalesData;
> > > END
> > >
> > >
> >
> >
>
>


Chris Keating (iAnywhere Solutions) Posted on 2004-02-27 05:43:08.0Z
From: "Chris Keating \(iAnywhere Solutions\)" <FightSpam_keating@iAnywhere.com>
Newsgroups: ianywhere.public.general
References: <403e9b3b@forums-1-dub> <403ea07b@forums-1-dub> <403ec612@forums-2-dub>
Subject: Re: Declare cursor end of procedure giving ERROR.
Lines: 210
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: vpn-concord-060.sybase.com
Message-ID: <403ed901@forums-2-dub>
X-Original-Trace: 26 Feb 2004 21:43:29 -0800, vpn-concord-060.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 26 Feb 2004 21:42:23 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 26 Feb 2004 21:43:08 -0800
X-Trace: forums-1-dub 1077860588 10.22.108.75 (26 Feb 2004 21:43:08 -0800)
X-Original-Trace: 26 Feb 2004 21:43:08 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2671
Article PK: 6146

Please read the documentation on the topic of compound statements.

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

****************************************************************************
*
Sign up today for your copy of the SQL Anywhere Studio 9 Developer Edition
and try out the market-leading database for mobile, embedded and small to
medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

****************************************************************************
*

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use CaseXpress to report bugs http://casexpress.sybase.com

****************************************************************************
*

"Chandresh Patel" <bapashree@yahoo.com> wrote in message
news:403ec612@forums-2-dub...
> Did you see my situation in procedure. I need cursor in while loop because
> each loop I get StartDate and EndDate and based on that create query and
> fetch it in to cursor.
>
> Is there any way or idea to declare at start and use in while loop for
> number of times.
>
> Thanks in Advance.
>
>
>
> "Chris Keating (iAnywhere Solutions)" <FightSpam_keating@iAnywhere.com>
> wrote in message news:403ea07b@forums-1-dub...
> > DECLAREs must occur at the start of a compound statement.
> >
> > --
> >
> > Chris Keating
> > Sybase Adaptive Server Anywhere Professional Version 8
> >
> >
>
****************************************************************************
> > *
> > Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition
> > and try out the market-leading database for mobile, embedded and small
to
> > medium sized business environments for free!
> >
> > http://www.ianywhere.com/promos/deved/index.html
> >
> >
>
****************************************************************************
> > *
> >
> > iAnywhere Solutions http://www.iAnywhere.com
> >
> > ** Please only post to the newsgroup
> >
> > ** Whitepapers can be found at http://www.iAnywhere.com/developer
> > ** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
> > ** Use CaseXpress to report bugs http://casexpress.sybase.com
> >
> >
>
****************************************************************************
> > *
> >
> > "Chandresh Patel" <bapashree@yahoo.com> wrote in message
> > news:403e9b3b@forums-1-dub...
> > > Sybase Anywhere 9.0.0.1108
> > >
> > > In my procedure i declare one cursor(crsr) at the starting and at the
> end
> > of
> > > procedure I declare another cursor (crsr1)
> > > in WHILE loop but at the time of saving procedure it giveing me error
> > like:
> > >
> > > [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access
> > > violation: near 'declare' in ... [declare] crsr1 cursor for...
> > > SQLCODE: -131
> > > SQLSTATE: 42000
> > >
> > >
> > > Here is my procedure
> > > ===============
> > >
> > > ALTER PROCEDURE "DBA"."GetSalesReportData"(IN mSGroupID Integer, IN
> > > ReportType TinyInt, IN sDate DATE, IN eDate DATE, IN cID Integer)
> > > BEGIN
> > > declare EOF_found exception for sqlstate value '02000';
> > > declare mStoreName varchar(40);
> > > declare mFieldData long varchar;
> > > DECLARE StartDate DATE;
> > > DECLARE EndDate DATE;
> > >
> > > declare crsr cursor for call GetStoreName(mSGroupID);
> > > open crsr;
> > >
> > > SET mFieldData = 'DisplayDates VARCHAR(30)';
> > >
> > > MyLoop: loop
> > > fetch next crsr into mStoreName;
> > > if sqlstate = EOF_found then
> > > leave MyLoop;
> > > else
> > > SET mStoreName = REPLACE(mStoreName, ' ', '');
> > > SET mStoreName = REPLACE(mStoreName, '*', '');
> > > if mFieldData IS NULL THEN
> > > SET mFieldData = STRING (mStoreName,' FLOAT');
> > > else
> > > SET mFieldData = STRING (mFieldData,', ',mStoreName,'
> > > FLOAT');
> > > end if;
> > > end if;
> > > end loop MyLoop;
> > >
> > > close crsr;
> > >
> > > SET mFieldData = STRING ('DECLARE LOCAL TEMPORARY TABLE
SalesData(',
> > > mFieldData, ' ) NOT TRANSACTIONAL');
> > > execute immediate mFieldData;
> > >
> > > //Get all sales Data.
> > > SET StartDate = sDate;
> > > SET EndDate = eDate;
> > >
> > > //Check Report Type
> > > IF ReportType = 0 THEN
> > > CALL GetCurrentWeekDates(StartDate, EndDate);
> > > ELSEIF ReportType = 1 THEN
> > > CALL GetCurrentYearDates(StartDate, EndDate);
> > > ELSEIF ReportType = 2 THEN
> > > CALL GetCurrentQuarterDates(StartDate, EndDate);
> > > ELSEIF ReportType = 3 THEN
> > > CALL GetCurrentMonthDates(StartDate, EndDate);
> > > ELSEIF ReportType = 4 THEN
> > > CALL GetCurrentDay(StartDate, EndDate);
> > > END IF;
> > >
> > > //Get sum of NetSales, SalesTax and CustomerCount
> > > WHILE sDate <= eDate LOOP
> > > declare crsr1 cursor for select ifnull(sum(A.DunkinSales), 0,
> > > sum(A.DunkinSales)) - (ifnull(sum(A.Overring), 0 , sum(A.Overring)) +
> > > ifnull(sum(A.SalesTax), 0, sum(A.SalesTax))) as NetSales from Sales A,
> > > DayActivity B where A.DayActivityID = B.DayActivityID and B.StoreID IN
> > > (select StoreID FROM StoreGroup WHERE StoreGroupID=mSGroupID) and
> > > CAST(DayDate AS DATE) BETWEEN StartDate and EndDate GROUP BY
B.StoreID;
> > >
> > > open crsr1;
> > >
> > > SET AllSales = '';
> > >
> > > MyLoop: loop
> > > fetch next crsr1 into mFValue;
> > > if sqlstate = EOF_found then
> > > leave MyLoop;
> > > else
> > > if mFValue IS NULL THEN
> > > SET mFValue = STRING (mFValue);
> > > else
> > > SET mFValue = STRING (mFValue,', ',mFValue);
> > > end if;
> > > end if;
> > > end loop MyLoop;
> > >
> > > close crsr1;
> > >
> > > //Check Report Type
> > > IF ReportType = 0 THEN
> > > CALL GetPreviousWeekDates(StartDate, EndDate);
> > > ELSEIF ReportType = 1 THEN
> > > CALL GetPreviousYearDates(StartDate, EndDate);
> > > ELSEIF ReportType = 2 THEN
> > > CALL GetPreviousQuarterDates(StartDate, EndDate);
> > > ELSEIF ReportType = 3 THEN
> > > CALL GetPreviousMonthDates(StartDate, EndDate);
> > > ELSEIF ReportType = 4 THEN
> > > CALL GetPreviousDay(StartDate, EndDate);
> > > END IF;
> > >
> > > SET eDate = EndDate;
> > > END LOOP;
> > >
> > > SELECT * FROM SalesData;
> > > END
> > >
> > >
> >
> >
>
>