Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2006
    Posts
    58

    Question Unanswered: 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!

  2. #2
    Join Date
    Aug 2006
    Posts
    58

    Question 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!

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd use an ORDER BY clause.

    -PatP

  5. #5
    Join Date
    Aug 2006
    Posts
    58
    Pat Phelan ,thank you very much.

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

    Anxoix to get your reply.

  6. #6
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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 [bold]not[/bold] 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.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Littlefoot
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    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

  9. #9
    Join Date
    Aug 2006
    Posts
    58
    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.

  10. #10
    Join Date
    Aug 2006
    Posts
    58
    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.
    Last edited by jp7234; 08-15-06 at 01:51.

  11. #11
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    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
    Last edited by rbackmann; 08-15-06 at 15:59.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •