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.

sql context

3 posts in General Discussion Last posting was on 2008-08-06 17:09:54.0Z
wcm Posted on 2008-08-04 22:24:15.0Z
Sender: 109e.48977ede.1804289383@sybase.com
From: wcm
Newsgroups: ianywhere.public.general
Subject: sql context
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4897818f.1100.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 4 Aug 2008 15:24:15 -0700
X-Trace: forums-1-dub 1217888655 10.22.241.41 (4 Aug 2008 15:24:15 -0700)
X-Original-Trace: 4 Aug 2008 15:24:15 -0700, 10.22.241.41
Lines: 24
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:6998
Article PK: 5221

I am a relative newbie to Sybase technologies. I am having
difficulty putting a (valid) common table expression into a
stored procedure or combining this statement with other
statements (such as DECLARE statements). It runs fine
standalone but generates a "syntax error near WITH" when
combined with any other statements or attempted to be
included in a stored procedure.

WITH [TempReference] AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [ID]
ASC) AS TheRow,
[ID],
[Description],
FullName
FROM [myDatabaseTable]
WHERE FullName Like 'M%'
)
SELECT [ID], [Description], FullName
FROM [TempReference]
WHERE (TheRow between 101 and 110)
Any ideas?
Thanks,
Chris


"Nick Elson" < Posted on 2008-08-05 16:52:40.0Z
From: "Nick Elson" <@@@nick@@@.@@@elson@sybase@@@.@@@com@@@>
Newsgroups: ianywhere.public.general
References: <4897818f.1100.1681692777@sybase.com>
Subject: Re: sql context
Lines: 59
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48988558@forums-1-dub>
Date: 5 Aug 2008 09:52:40 -0700
X-Trace: forums-1-dub 1217955160 10.22.241.152 (5 Aug 2008 09:52:40 -0700)
X-Original-Trace: 5 Aug 2008 09:52:40 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7002
Article PK: 5227

Since I don't have your data or schema I did this with the
V10 server and demo.db and it seems to work fine for
me doing this. The DECLARES are just there taking the
place of your procedure's parameters.

Begin
DECLARE n_start integer;
DECLARE n_end integer;
DECLARE str_like char(10);
set n_start=5;
set n_end=7;
set str_like ='M%';

WITH [vEmploy] AS
(
SELECT ROW_NUMBER( ) over (ORDER BY [EmployeeID] asc) TheRow,
[EmployeeID],
[Street],
GivenName || ' ' || Surname FullName
FROM [Employees]
-- WHERE FULLName Like 'M%'
WHERE FULLName Like str_like
)
SELECT TheRow,
[EmployeeID],
[Street],
FullName
from [vEmploy]
WHERE (TheRow between n_start and n_end);
end

<wcm> wrote in message news:4897818f.1100.1681692777@sybase.com...
>I am a relative newbie to Sybase technologies. I am having
> difficulty putting a (valid) common table expression into a
> stored procedure or combining this statement with other
> statements (such as DECLARE statements). It runs fine
> standalone but generates a "syntax error near WITH" when
> combined with any other statements or attempted to be
> included in a stored procedure.
>
> WITH [TempReference] AS
> (
> SELECT ROW_NUMBER() OVER (ORDER BY [ID]
> ASC) AS TheRow,
> [ID],
> [Description],
> FullName
> FROM [myDatabaseTable]
> WHERE FullName Like 'M%'
> )
> SELECT [ID], [Description], FullName
> FROM [TempReference]
> WHERE (TheRow between 101 and 110)
> Any ideas?
> Thanks,
> Chris


"Frank Ploessel" <fpl... Posted on 2008-08-06 17:09:54.0Z
Subject: Re: sql context
From: "Frank Ploessel" <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m>
Content-Type: text/plain; format=flowed; delsp=yes; charset=iso-8859-15
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
References: <4897818f.1100.1681692777@sybase.com>
Content-Transfer-Encoding: 7bit
Message-ID: <op.ufg5yro3j0bybf@bonw00164.internal.imsglobal.com>
User-Agent: Opera Mail/9.21 (Win32)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 6 Aug 2008 10:09:54 -0700
X-Trace: forums-1-dub 1218042594 10.22.241.152 (6 Aug 2008 10:09:54 -0700)
X-Original-Trace: 6 Aug 2008 10:09:54 -0700, vip152.sybase.com
Lines: 34
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:7012
Article PK: 5236

Chris,

Maybe the problem is not with the statement you showed us. Can you post
the complete source code of the procedure causing the problem?

Frank

On Tue, 05 Aug 2008 00:24:15 +0200, <wcm> wrote:

> I am a relative newbie to Sybase technologies. I am having
> difficulty putting a (valid) common table expression into a
> stored procedure or combining this statement with other
> statements (such as DECLARE statements). It runs fine
> standalone but generates a "syntax error near WITH" when
> combined with any other statements or attempted to be
> included in a stored procedure.
>
> WITH [TempReference] AS
> (
> SELECT ROW_NUMBER() OVER (ORDER BY [ID]
> ASC) AS TheRow,
> [ID],
> [Description],
> FullName
> FROM [myDatabaseTable]
> WHERE FullName Like 'M%'
> )
> SELECT [ID], [Description], FullName
> FROM [TempReference]
> WHERE (TheRow between 101 and 110)
> Any ideas?
> Thanks,
> Chris