HOW TO GET THE RESULTS IN THE SAME SEQUENCE IN ORACLE 9i AND SQL SERVER 2005?

In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!In ORACLE 9i, I created the table test that show the tree structure of an organizaion with the following SQL statement:

CREATE TABLE TEST(
PARFOLDERNO NUMBER(8,0),
FOLDERNO NUMBER(8,0)
)

And select the data using the following SQL Statement:
SELECT PARFOLDERNO,FOLDERNO FROM TEST

The result is:
PARFOLDERNO FOLDERNO
0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

To show the subnodes of the root node 2461, the following SQL Statement is used:

SELECT PARFOLDERNO,FOLDERNO FROM TEST START WITH FOLDERNO=2461 CONNECT BY PRIOR FOLDERNO=PARFOLDERNO

the results:

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470
2461 2462
2462 2465
2462 2466

I have created the table test with the same structure and the same data in SQL Server 2005. To show the subnodes of the root node 2461, the following SQL Statement is used:

WITH CTE_TEST(PARFOLDERNO,FOLDERNO)
AS
(
SELECT PARFOLDERNO,FOLDERNO FROM TEST WHERE FOLDERNO=2461
UNION ALL

SELECT TEST.PARFOLDERNO,TEST.FOLDERNO FROM TEST, CTE_TEST
WHERE TEST.PARFOLDERNO=CTE_TEST.FOLDERNO
)

SELECT PARFOLDERNO,FOLDERNO FROM CTE_TEST

PARFOLDERNO FOLDERNO

0 2461
2461 2463
2461 2462
2462 2465
2462 2466
2463 2469
2463 2470

The results are shown again in Oracle 9i and SQL Server 2005 as follwos:

Oracle 9i SQL Server 2005

PARFOLDERNO FOLDERNO PARFOLDERNO FOLDERNO
0 2461 0 2461
2461 2463 2461 2463
2463 2469 2461 2462
2463 2470 2462 2465
2461 2462 2462 2466
2462 2465 2463 2469
2462 2466 2463 2470

How can I get the result with the same sequence in SQL Server 2005?

Thanks!|||Just to make it clear: Oracle result is correct, SQL Server 2005 result is wrong?

If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.|||I'd use an ORDER BY clause.

-PatP|||Pat Phelan ,thank you very much.

Where is the clause "Order by " added?Please give me some example.

Anxoix to get your reply.|||You will have to use an ORDER BY somewhere. Not even Oracle guarantees that the results will always be returned in the same order if you don't use an ORDER BY.
Remember you are dealing with (mathematical) sets which do not have an implicit order. A DBMS is not a spreadsheet.

Using CONNECT BY does impose an implicit order, that's why in your statement it's very likely that the sorting will stay that way in Oracle, but if you want the SQL Server result sorted, tell the server to do so.|||If that's so, I guess you should have asked this question on SQL Server forum, not Oracle.he did, he posted in both, and the duplicate was removed, so he re-posted another duplicate, and now they are merged

jp7234, please do not cross-post|||both platforms same syntax for this

select blah
from table
where 1=1
order by blah

select blah
from table
where 1=1
union
select blah
from table1
where 1=1
order by blah

Or order by can use column number to order

IE order by 1, 2|||r937,thanks.

Thank you for your care. I wll not give the same questions in different forums.This time, for I am anxious to get help as soon as possible I do so.|||Thank you,all of above friends.

I have used the Clause "Order by",but I failed.

The results in Oracle 9i shows a tree-like structure.

PARFOLDERNO FOLDERNO
0 2461
2461 2463
2463 2469
2463 2470

2461 2462
2462 2465
2462 2466

We can find four branches:
1.the first 4 records show two branches
(1) 2461--2463--2469
(2) 2461--2463--2470

2.the first record and No. 5-7 record show other two branches
(3) 2461--2462--2465
(4) 2461--2462--2466

We can find that Parent node(0,2461) have two direct children node(2461,2463).(2461,2462). And the node information of the children node(2461,2463).(2461,2462) is sequencecd in different block.

But the results in SQL Server 2005 can not show these brances.And the results can not be sequenced in different block. I want to get the results with tree-like structure (the first branche,the second branche,and ....)in SQL Server 2005.

Please help me. If possible ,please give me your emails so that I can describe what I need in the results.

Thank a lots.|||I think that what you are describing here are analtic functions native to Oracle
CONNECT BY PRIOR

To my knowledge Sql Server does not have this functionallity at this time.

The only way to accomplish what I think that you are tring to accomplish is within a SP and build your result while cursoring through recordsets.

If you have a defined/finite number og related items
ie grand parent
Parent
child

You can accomplish your task using you table joined to itself.

select lvl1.Parent_node
, lvl2.Parent_node
, lvl3.Parent_node
from FROM TEST lvl1
left outer join test lvl2 on lvl1.folder_node =lvl2.Parent_node
left outer join test lvl3 on lvl2.folder_node =lvl1.Parent_node|||This can be done in TSQL, without using cursors, and without using recursion.

But before I go to the (not inconsiderable) trouble of explaining how, please explain WHY it is so important that the data be returned in that particular order. Data ordering is normally irrelevant to the database server, whether Oracle or MSSQL. Oracle just "happens" to return the data in that order for this particular non-standard function, so don't expect it to be a simple matter to force MSSQL to follow another engine's internal logic.