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.

What's wrong with my query?

3 posts in General Discussion Last posting was on 2006-07-25 00:46:45.0Z
Chris Ceniza Posted on 2006-07-24 08:32:18.0Z
Sender: 2319.44c4850f.1804289383@sybase.com
From: Chris Ceniza
Newsgroups: ianywhere.public.general
Subject: What's wrong with my query?
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <44c48592.231d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 24 Jul 2006 01:32:18 -0700
X-Trace: forums-1-dub 1153729938 10.22.241.41 (24 Jul 2006 01:32:18 -0700)
X-Original-Trace: 24 Jul 2006 01:32:18 -0700, 10.22.241.41
Lines: 36
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5517
Article PK: 1556

I started studying common table expressions. First I used
DBISQL and the query worked alright. But when I transfered
my query to a stored procedure It won't save. I keeps saying
error line "4" and points to the "WITH" reserved word. Im
using ASA 9.0.2 3320 WIN2000.

what's wrong with this?

CREATE PROCEDURE
"DBA"."sp_prodis_bill_of_materials"(@itemcode char(25))
/* RESULT( column_name column_type, ... ) */
BEGIN
WITH RECURSIVE
parts(itemcode, partcode, req_qty) as
( select itemcode,
partcode,
req_qty
from itemfilebom
where itemcode = @itemcode

union all

select b.itemcode,
b.partcode,
req_qty = a.req_qty * b.req_qty
from parts a,
itemfilebom b
where a.partcode = b.itemcode
)
select partcode, sum(req_qty) as quantity
from parts
where partcode not in ( select itemcode from
itemfilebom )
group by partcode
order by partcode
END


"Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g Posted on 2006-07-24 13:39:21.0Z
From: "Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com>
Organization: iAnywhere Solutions
User-Agent: Thunderbird 1.5.0.4 (Windows/20060516)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: What's wrong with my query?
References: <44c48592.231d.1681692777@sybase.com>
In-Reply-To: <44c48592.231d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: vpneast-client-017.sybase.com
Message-ID: <44c4cbc3$1@forums-2-dub>
X-Original-Trace: 24 Jul 2006 06:31:47 -0700, vpneast-client-017.sybase.com
Lines: 38
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 24 Jul 2006 06:31:48 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 24 Jul 2006 06:39:21 -0700
X-Trace: forums-1-dub 1153748361 10.22.108.75 (24 Jul 2006 06:39:21 -0700)
X-Original-Trace: 24 Jul 2006 06:39:21 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5518
Article PK: 1558

Change

req_qty = a.req_qty * b.req_qty
to

a.req_qty * b.req_qty as req_qty
--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

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

Register now for TechWave 2006 August 6-10 in Las Vegas. New! 2-day
and 4-day options for SQL Anywhere technical training.
Sessions will focus on data management, data movement, and SQL Anywhere
10. Visit www.sybase.com/techwave


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

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 Case Express to report bugs http://case-express.sybase.com

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