none
User Defined Function Table Joined to itself RRS feed

  • Question

  • I'm trying to create a UserDefined Function with a table that's joined to itself.  For Example tblEmployee INNER JOIN tblEmployee on EmployeeID=EmployeeID and tblEmployee.HireDate < tblEmployee.HireDate.   I  first designed this using the VIew Designer.  In the View Designer it shows tblEmployee INNER JOIN tblEmployee_1.  But Query Analyzer doesn't recognize tblEmployee_1.  So at the current time it is displaying tblEmployee INNER JOIN tblEmployee.  This produces the wrong result as it appears to be joining the table to itself.  Any Suggestions on how to identify different instances of the table in a User Defined Function.

    Thanks for your help.

    PS.  I'm using SQL Server 2000.  Not my idea.  Customer's idea. 

    Wednesday, June 9, 2010 12:55 AM

Answers

  • You need to give your table an alias in the query and refer to it by alias, e.g.

     

    select e1.EmployeeID, e1.EmpName from tblEmployee e1 inner join tblEmployee e2 on e1.EmployeeID = e2.ManagerID ....


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, June 9, 2010 4:28 AM
    Moderator

All replies

  • Hi deepPowdah,

    I empathise with the fact you are made to work with SQL 2000. However I do understand that basically there is no difference between simple user defined functions (UDF) and self join queries between SQL 2000 and SQL 2005 so I should be able to help.

    Please post a version of your user defined function definition, we can then look at getting the self join to work, then getting the UDF to work.

    Kind Regards,

    Kieran.


    If you have found any of my posts helpful then please vote them as helpful. Kieran Wood PGD SoftDev (Open), MBCS http://uk.linkedin.com/in/bileeds
    Wednesday, June 9, 2010 2:21 AM
  • You need to give your table an alias in the query and refer to it by alias, e.g.

     

    select e1.EmployeeID, e1.EmpName from tblEmployee e1 inner join tblEmployee e2 on e1.EmployeeID = e2.ManagerID ....


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    Wednesday, June 9, 2010 4:28 AM
    Moderator