problems with resursive query, cmd WITH problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problems with resursive query, cmd WITH problems

Author

Topic

schnulli887
Starting Member

3 Posts

Posted - 2010-03-31 : 06:29:20

hello altogehter,

i am using a sql server 2000 and i would like to build a recursive query (i want to read the ebay-category structure).

while searching the web a found a code snippet that looked good to me. but i always get the following error:

quote:
Error: Falsche Syntax in der Nähe des WITH-Schlüsselwortes. (State:37000, Native Code: 9C)

code is as follows:

use ebay
go

CREATE TABLE MyCTE
(
  article_no char(20)
)

WITH MyCTE( article_no) AS
(
  SELECT top 10 article_no
  FROM tblAuction
)
SELECT *
FROM MyCTE

i know this has nothing to do with recursive queries, but when i got the error i tried to kick out all possible errors.

looks to me my sql server doesn't understand the WITH command?
but why?

i am using sql server 2000 ver5.1?
anything else you need?

any ideas?

desperateley... :-)

matt

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-31 : 06:40:36

You have misunderstood.
First: a CTE needs no create because it is "a kind" of temp table.
Second: a CTE works not in SQL Server 2000, introducing was in SQL Server 2005.

No, you're never too old to Yak'n'Roll if you're too young to die.

raky
Aged Yak Warrior

767 Posts

Posted - 2010-03-31 : 06:46:17

Common Table Expressions (CTE's) work from SQL server 2005 onwards. It doesn't work with sql server 2000.

schnulli887
Starting Member

3 Posts

Posted - 2010-03-31 : 07:19:30

ok,

understood.
is there any other possibility to create recursive queries?

the problem is the depth of the categories i want to analyze is different, sometimes 3 sometimes 7 or 5.

if i use a equi join like this

select  a.cat_desc as level0,
        b.cat_desc as level1,
        c.cat_desc as level2,
        d.cat_desc as level3,
        e.cat_desc as level4,
        f.cat_desc as level5
from    tblCategory a,
        tblCategory b,
        tblCategory c,
        tblCategory d,
        tblCategory e,
        tblCategory f
where a.cat_id = 0
and a.cat_id = b.cat_parent
and b.cat_id = c.cat_parent
and c.cat_id = d.cat_parent
and d.cat_id = e.cat_parent
and e.cat_id = f.cat_parent
order by a.cat_desc, b.cat_desc, c.cat_desc, d.cat_desc, e.cat_desc, f.cat_desc;

i don't get the full depth. categories get lost because of the equi-join in the deeper levels. but i need the complete depth of the structure.

any hint?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-31 : 08:05:40

I believe this can help:
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql

No, you're never too old to Yak'n'Roll if you're too young to die.

schnulli887
Starting Member

3 Posts

Posted - 2010-03-31 : 08:53:29

thx a lot!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-31 : 09:18:58