I have a table of Departments.  One of the fields is the Department the
Department reports to (a reporting hierarchy).  Not all Departments will
have a parent.  I want to fetch the parent department name but am having
a problem with the self join.

Department:
  DeptID
  DeptName
  OwnerID

Here is the query I'm trying

select D.DeptID, D.DeptName, O.DeptName
  from Department D left outer join Department O
      on D.OwnerID = O.DeptID

I'm getting the following error:
ElevateDB Error #700 An error was found in the statement at line 3 and
column 22 (Expected Char, GUID, or VarChar expression but instead found
"O"."DeptID")

Can anyone point me in the right direction?
--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com

Gordon


I just tried the same here with no problem. It could be complaining when there are nulls (couldn't try that since my table uses integers and has a zero default) so try something like

select D.DeptID, D.DeptName, O.DeptName
  from Department D left outer join Department O
      on D.OwnerID = COALESCE(O.DeptID,'')


Roy Lambert [Team Elevate]

Gordon,

<< Here is the query I'm trying

select D.DeptID, D.DeptName, O.DeptName
  from Department D left outer join Department O
      on D.OwnerID = O.DeptID

I'm getting the following error:
ElevateDB Error #700 An error was found in the statement at line 3 and
column 22 (Expected Char, GUID, or VarChar expression but instead found
"O"."DeptID") >>

What are the data types of the columns involved ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
>
> What are the data types of the columns involved ?

Doh!  I'm trying to match integer and varchar columns.  That's what
comes from picking up an old project without reviewing your data.

--
Gordon Turner
Mycroft Computing
http://www.mycroftcomputing.com