Recursive Queries in SQL Version 8.0 (SQL 2000)

Question

Recursive Queries in SQL Version 8.0 (SQL 2000)

ruggierir

SSC Journeyman

Points: 83
More actions
January 24, 2008 at 7:38 am

#178433

I need to solve a recursive Query problem and
I have been attempting to perform a recursive query as illustrated in the article "Recursive Queries in SQL:1999 and SQL Server 2005" ( http://www.sqlservercentral.com/articles/SQL+Server+2005+-+TSQL/recursivequeriesinsql1999andsqlserver2005/1846/ ).
Is this possible in SQL version 8.0 (SQL 2000)? I am trying the example code:
WITH
tree (data, id)
AS (SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE
WHERE VHC_NAME = 'MOTORCYCLE'
UNION ALL
SELECT VHC_NAME, VHC_ID_FATHER
FROM T_VEHICULE V
INNER JOIN tree t
ON t.id = V.VHC_ID)
SELECT *
FROM tree
The sample query keeps returning an error
"Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'WITH'."

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 1

Common Table Expressions are a new feature to SQL 2005. That syntax does not work on SQL 2000.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

Your "WITH" that it is complaining about is part of the CTE (common table expression).

CTE's are not supported in SQL 2000.

Recursive queries are not supported in SQL 2000.

There are a bunch of options in SQL 2000, but this is not one of them.

ruggierir SSC Journeyman Points: 83 More actions · Answer 3

You mentioned that there are other options in SQL2000, where may I find them? Is there a WEB site to reference? Do you have examples?

Jeremy-475548 Hall of Fame Points: 3065 More actions · Answer 4

When I've had to follow a hierarchical relationship in SQL 2000, I usually do the following:

1. create a temp table to store my results, with a Level column

2. "seed" the temp table with my top level items (those items that have no parent) with an INSERT INTO statement

3. create a loop inserting records into the temp table limiting on records where level = level -1 until @@rowcount is 0

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 5

Do a google search for expanding hierarchies for some good examples of an iterative process replacing a recursive one.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

ruggierir SSC Journeyman Points: 83 More actions · Answer 6

Yes, Thanks.

It just goes to show you, just like the movie "iRobot"

if you don't ask the correct questions, you won't get the correct answers......

prior to this, I tried surfing the web for hours lookin for "Recursive SQL ...." Etc., with no results.

Thanks again.