How can I get the functionality of CONNECT BY PRIOR of Oracle in SQL Server 2000/2005/2008?
CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ... PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.
Linked Servers are a method by which a SQL Server can talk to another ODBC compliant database, such as another SQL Server instance or an Oracle database, with a direct T-SQL query. There are several important settings to know when setting up a Linked Server.
To migrate Oracle databases to SQL Server, you must connect to the Oracle database that you want to migrate. When you connect, SSMA obtains metadata about all Oracle schemas, and then displays it in the Oracle Metadata Explorer pane.
Open SQL Server Management Studio(SSMS), go to Server Objects and then Linked Servers. Right click on Linked Servers and then click on New Linked Server as depicted below. Give Linked Server Name as per your naming convention. Select Oracle Provider for OLEDB in Provider.
The SQL standard way to implement recursive queries, as implemented e.g. by IBM DB2 and SQL Server, is the WITH
clause. See this article for one example of translating a CONNECT BY
into a WITH
(technically a recursive CTE) -- the example is for DB2 but I believe it will work on SQL Server as well.
Edit: apparently the original querant requires a specific example, here's one from the IBM site whose URL I already gave. Given a table:
CREATE TABLE emp(empid INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(10),
salary DECIMAL(9, 2),
mgrid INTEGER);
where mgrid
references an employee's manager's empid
, the task is, get the names of everybody who reports directly or indirectly to Joan
. In Oracle, that's a simple CONNECT
:
SELECT name
FROM emp
START WITH name = 'Joan'
CONNECT BY PRIOR empid = mgrid
In SQL Server, IBM DB2, or PostgreSQL 8.4 (as well as in the SQL standard, for what that's worth;-), the perfectly equivalent solution is instead a recursive query (more complex syntax, but, actually, even more power and flexibility):
WITH n(empid, name) AS
(SELECT empid, name
FROM emp
WHERE name = 'Joan'
UNION ALL
SELECT nplus1.empid, nplus1.name
FROM emp as nplus1, n
WHERE n.empid = nplus1.mgrid)
SELECT name FROM n
Oracle's START WITH
clause becomes the first nested SELECT
, the base case of the recursion, to be UNION
ed with the recursive part which is just another SELECT
.
SQL Server's specific flavor of WITH
is of course documented on MSDN, which also gives guidelines and limitations for using this keyword, as well as several examples.
@Alex Martelli
's answer is great!
But it work only for one element at time (WHERE name = 'Joan'
)
If you take out the WHERE
clause, the query will return all the root rows together...
I changed a little bit for my situation, so it can show the entire tree for a table.
table definition:
CREATE TABLE [dbo].[mar_categories] (
[category] int IDENTITY(1,1) NOT NULL,
[name] varchar(50) NOT NULL,
[level] int NOT NULL,
[action] int NOT NULL,
[parent] int NULL,
CONSTRAINT [XPK_mar_categories] PRIMARY KEY([category])
)
(level
is literally the level of a category 0: root, 1: first level after root, ...)
and the query:
WITH n(category, name, level, parent, concatenador) AS
(
SELECT category, name, level, parent, '('+CONVERT(VARCHAR (MAX), category)+' - '+CONVERT(VARCHAR (MAX), level)+')' as concatenador
FROM mar_categories
WHERE parent is null
UNION ALL
SELECT m.category, m.name, m.level, m.parent, n.concatenador+' * ('+CONVERT (VARCHAR (MAX), case when ISNULL(m.parent, 0) = 0 then 0 else m.category END)+' - '+CONVERT(VARCHAR (MAX), m.level)+')' as concatenador
FROM mar_categories as m, n
WHERE n.category = m.parent
)
SELECT distinct * FROM n ORDER BY concatenador asc
(You don't need to concatenate the level
field, I did just to make more readable)
the answer for this query should be something like:
I hope it helps someone!
now, I'm wondering how to do this on MySQL... ^^
I haven't used connect by prior, but a quick search shows it's used for tree structures. In SQL Server, you use common table expressions to get similar functionality.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With