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.

For Loop

2 posts in General Discussion Last posting was on 2003-08-11 16:04:46.0Z
JacobT Posted on 2003-08-11 15:01:21.0Z
Sender: 18e8.3f37afb5.1804289383@sybase.com
From: JacobT
Newsgroups: ianywhere.public.general
Subject: For Loop
X-Mailer: WebNews to Mail Gateway v1.1s
Message-ID: <3f37afd5.18e9.846930886@sybase.com>
X-Original-NNTP-Posting-Host: 10.22.241.42
X-Original-Trace: 11 Aug 2003 08:01:41 -0700, 10.22.241.42
Lines: 55
NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
Date: 11 Aug 2003 08:01:21 -0700
X-Trace: forums-1-dub 1060614081 10.22.241.42 (11 Aug 2003 08:01:21 -0700)
X-Original-Trace: 11 Aug 2003 08:01:21 -0700, forums-2-dub.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1558
Article PK: 3787

I'm trying to run a couple of queries inside of the for loop
below. I've narrowed it down through my debugging and
everything works properly until I put in the for loop. The
queries themselves work and all the variables are declared
properly but I'm missing something on the For Loop
somewhere. The only thing the error message that I get says
is "Syntax Error near FOR" so it's not real good at
specifics. Any help someone could give me would be great.
This is my first time trying to loop with SQL so it's a
learning experience for sure. :) Thanks a lot in advance :)


DECLARE @ThisResult char(254)
DECLARE @CompareResult char(254)

--Create Temp table
Create TABLE #results (
AR_Invoices_Link_Code AUTOLINKCODE PRIMARY KEY,
Storers_Link_Code INTEGER NOT NULL,
Cost_Centers_Link_Code INTEGER NOT NULL,
Date_Added DATEADDED,
Date_Edited DATEEDITED,
User_Added USERADDED,
User_Edited USEREDITED,
Invoice CHAR(254) NOT NULL,
Date_Invoiced TIMESTAMP NOT NULL,
Description CHAR(254),
Invoice_Total Decimal(19,4),
Reference_Number CHAR(254)
)

BEGIN
FOR GeneralFor as c CURSOR FOR SELECT Invoice FROM
Database.Filename Group by Invoice Having Count(Invoice) > 1
Open c
Select @CompareResult = null
Select @ThisResult = null

FETCH c INTO @ThisResult
DO
IF @ThisResult <> @CompareResult
Select @CompareResult = @ThisResult
ELSE IF @ThisResult = @CompareResult
SELECT * INTO #Results FROM database.filename as ar WHERE
ar.Invoice = @ThisResult
FETCH c INTO @ThisResult
END FOR;
END

Select * From #Results
Drop Table #Results

--Clean up cursor variables
Close c
Deallocate c


Nick Elson Posted on 2003-08-11 16:04:46.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <3f37afd5.18e9.846930886@sybase.com>
Subject: Re: For Loop
Lines: 78
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: nicelson-xp.sybase.com
Message-ID: <3f37beb2@forums-2-dub>
X-Original-Trace: 11 Aug 2003 09:05:06 -0700, nicelson-xp.sybase.com
NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
Date: 11 Aug 2003 09:04:46 -0700
X-Trace: forums-1-dub 1060617886 10.22.241.42 (11 Aug 2003 09:04:46 -0700)
X-Original-Trace: 11 Aug 2003 09:04:46 -0700, forums-2-dub.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1559
Article PK: 3788

The FOR statement is not a valid TSQL command
.... and so ....
this is a simple case of a mixed dialect usage that is just not
permitted.

I see nothing in the part 'that works' that indicates you
ever intended this to be anything but TSQL so you are
going to be restricted to
DECLARE
WHILE
OPEN
FETCH
CONTINUE
TSQL syntax,

**UNLESS**

you are prepared to rewrite this whole procedure to use WSQL.

<JacobT> wrote in message news:3f37afd5.18e9.846930886@sybase.com...
> I'm trying to run a couple of queries inside of the for loop
> below. I've narrowed it down through my debugging and
> everything works properly until I put in the for loop. The
> queries themselves work and all the variables are declared
> properly but I'm missing something on the For Loop
> somewhere. The only thing the error message that I get says
> is "Syntax Error near FOR" so it's not real good at
> specifics. Any help someone could give me would be great.
> This is my first time trying to loop with SQL so it's a
> learning experience for sure. :) Thanks a lot in advance :)
>
>
> DECLARE @ThisResult char(254)
> DECLARE @CompareResult char(254)
>
> --Create Temp table
> Create TABLE #results (
> AR_Invoices_Link_Code AUTOLINKCODE PRIMARY KEY,
> Storers_Link_Code INTEGER NOT NULL,
> Cost_Centers_Link_Code INTEGER NOT NULL,
> Date_Added DATEADDED,
> Date_Edited DATEEDITED,
> User_Added USERADDED,
> User_Edited USEREDITED,
> Invoice CHAR(254) NOT NULL,
> Date_Invoiced TIMESTAMP NOT NULL,
> Description CHAR(254),
> Invoice_Total Decimal(19,4),
> Reference_Number CHAR(254)
> )
>
> BEGIN
> FOR GeneralFor as c CURSOR FOR SELECT Invoice FROM
> Database.Filename Group by Invoice Having Count(Invoice) > 1
> Open c
> Select @CompareResult = null
> Select @ThisResult = null
>
> FETCH c INTO @ThisResult
> DO
> IF @ThisResult <> @CompareResult
> Select @CompareResult = @ThisResult
> ELSE IF @ThisResult = @CompareResult
> SELECT * INTO #Results FROM database.filename as ar WHERE
> ar.Invoice = @ThisResult
> FETCH c INTO @ThisResult
> END FOR;
> END
>
> Select * From #Results
> Drop Table #Results
>
> --Clean up cursor variables
> Close c
> Deallocate c