[mrgordonz]

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

[andrewst]

Oracle doesn't allow a FROM clause in an UPDATE statement. Instead you have to use a correlated subquery, something like this:

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

[anacedent]

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.

[mrgordonz]

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

[mrgordonz]

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:

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

[andrewst]

True, that WHILE statement won't work. It is equivalent to:

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

[mrgordonz]

Hi Tony,

Thanks - that fixed it.

Your help is muchly appreciated.

Cheers,

Paul Hobbs

[anacedent]

What happens when the data changes?

[mrgordonz]

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