Results 1 to 9 of 9
  1. #1
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10

    Unanswered: URGENT: UPDATE statement that includes an INNER JOIN

    Hi All,

    I am relatively new to Oracle, so I could be making a very stupid newbie-error. What I am trying to do is a simple thing in SQL Server, but I am stumped at how to do it in Oracle. (Sorry about the long post.)

    Basically I am trying to port the following solution for SQL Server to Oracle:
    http://www.sqlteam.com/item.asp?ItemID=8866

    The idea is that it uses a simple table that makes it easy to work with hierarchical structures (eg: reporting relationships, org charts, etc). I have tried using the CONNECT BY syntax for what I am trying to do, but it is not flexible enough for what I want to do.

    The table that the above article describes has the following fields:
    Node
    ParentNode
    EmployeeID
    Depth
    Lineage

    I am stuck on the section that updates the Depth and Lineage fields according to the Node and ParentNode data. The SQL code that the author uses in SQL Server is:


    WHILE EXISTS (SELECT * FROM Tree WHERE Depth Is Null)
    UPDATE T
    SET
    T.depth = P.Depth + 1,
    T.Lineage = P.Lineage + Ltrim(Str(T.ParentNode,6,0)) + '/'
    FROM
    Tree AS T INNER JOIN
    Tree AS P ON (T.ParentNode=P.Node)
    WHERE
    P.Depth>=0
    AND P.Lineage Is Not Null
    AND T.Depth Is Null


    I have slightly changed the code as follows:


    WHILE EXISTS (SELECT * FROM cnt_employee_tree WHERE Depth IS NULL)
    LOOP
    UPDATE tree_emp
    SET
    tree_emp.Depth = tree_mgr.Depth + 1,
    tree_emp.Lineage = tree_mgr.Lineage + LTRIM(TO_CHAR(tree_emp.ParentNode)) + '/'
    FROM
    cnt_employee_tree AS tree_emp,
    cnt_employee_tree AS tree_mgr
    WHERE
    tree_emp.Parent_Node = tree_mgr.Node
    AND tree_mgr.Depth >= 0
    AND tree_mgr.Lineage Is Not Null
    AND tree_emp.Depth Is Null
    END LOOP;


    I am trying to create a Procedure that executes the above code, but I get three compilation errors (using TOAD). One of them is below:


    Oracle Error PLS-00103: Encountered the symbol "FROM" when expecting one of the following:

    . ( , * @ % & - + ; / at mod rem return returning
    <an exponent (**)> where ||
    The symbol "return was inserted befor


    The other two errors are at the WHERE and END LOOP parts of the code.

    I suspect that the problem is the fact that there is a join in the UPDATE statement.

    This has become quite an urgent problem for me and I am completely stumped! Any help would be greatly appreciated.

    Regards,

    Paul Hobbs

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Oracle doesn't allow a FROM clause in an UPDATE statement. Instead you have to use a correlated subquery, something like this:
    Code:
    UPDATE cnt_employee_tree tree_emp
    SET (tree_emp.Depth, tree_emp.Lineage) =
    ( SELECT tree_mgr.Depth + 1, tree_mgr.Lineage + LTRIM(TO_CHAR(tree_emp.ParentNode)) + '/' 
      FROM   cnt_employee_tree tree_mgr
      WHERE  tree_emp.Parent_Node = tree_mgr.Node
      AND tree_mgr.Depth >= 0 
      AND tree_mgr.Lineage Is Not Null 
    )
    WHERE tree_emp.Depth Is Null

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    visit http://asktom.oracle.com and do a keyword search on CONNECT BY
    It is not clear to me why you want to store computed values in the table.
    If it is primarily for ease of subsequent processing, you might want to consider
    creating a VIEW which masks the "complexity" of the connect from folks.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    Hi anacedent,

    The reason I can't use a CONNECT BY is that when I eventually get this problem resolved, I will be creating a VIEW that will be used by a Java application that passes arguments in a very convoluted manner, and unfortunately the application won't allow me to specify arguments for a CONNECT BY query. It is horrible and complicated (and I don't fully understand how the application actually works!), but I know that a CONNECT BY won't help me.

    But thanks for the suggestion.

    Regards,

    Paul Hobbs

  5. #5
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    Hi Tony,

    That was excellent - thank you! Unfortunately I am getting another error:

    PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only

    My code is as follows:
    Code:
    WHILE EXISTS (SELECT * FROM cnt_employee_tree WHERE Depth IS NULL) 
    LOOP
      UPDATE cnt_employee_tree tree_emp
      SET (tree_emp.Depth, tree_emp.Lineage) =
        ( SELECT tree_mgr.Depth + 1, tree_mgr.Lineage + LTRIM(TO_CHAR(tree_emp.ParentNode)) + '/' 
        FROM   cnt_employee_tree tree_mgr
        WHERE  tree_emp.Parent_Node = tree_mgr.Node
        AND tree_mgr.Depth >= 0 
        AND tree_mgr.Lineage Is Not Null 
        )
      WHERE tree_emp.Depth Is Null;
    END LOOP;
    I have done some searching for this error, and this is what I found:
    http://www.cise.ufl.edu/help/databas...6525/pcmus.htm

    but unfortunately I don't really understand what the problem is or how to fix it.

    Thanks in advance,

    Paul Hobbs

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    True, that WHILE statement won't work. It is equivalent to:
    Code:
    LOOP
      UPDATE cnt_employee_tree tree_emp
      SET (tree_emp.Depth, tree_emp.Lineage) =
        ( SELECT tree_mgr.Depth + 1, tree_mgr.Lineage + LTRIM(TO_CHAR(tree_emp.ParentNode)) + '/' 
        FROM   cnt_employee_tree tree_mgr
        WHERE  tree_emp.Parent_Node = tree_mgr.Node
        AND tree_mgr.Depth >= 0 
        AND tree_mgr.Lineage Is Not Null 
        )
      WHERE tree_emp.Depth Is Null;
      EXIT WHEN SQL%ROWCOUNT = 0;
    END LOOP;
    i.e. stop when you have no more records to update.

    (Note that this logic could result in an infinite loop if the data is wrong!)

  7. #7
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    Hi Tony,

    Thanks - that fixed it.

    Your help is muchly appreciated.

    Cheers,

    Paul Hobbs

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    What happens when the data changes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    May 2004
    Location
    Brisbane, Australia
    Posts
    10
    There is a procedure that updates rebuilds this table each night:
    1. It adds the employee id of new employees
    2. It nulls the Parent_node, depth and lineage
    3. It updates the parent_node (in case reporting relationships have changed in the source table)
    4. It updates the depth and lineage data

    I grant you, it isn't the most elegant of solutions, and I would prefer not to have to use a separate table, but I don't know of any other solution.

    If you like I can give you more detailed info about what I am trying to accomplish and the constraints I am working under, and perhaps you will see a better solution.

    Cheers,

    Paul Hobbs

Posting Permissions

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