Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    Unanswered: Inner join in user-defined function

    Hello all,

    can anyone please help with this little issue:

    I have a user-defined function that works on two tables. First has more records than the second and therefore i am trying to use an inner join to limit the number of records to search by. However, the function does not seem to understand this and for all records in first table which do not have corresponding records in the second table returns null in @cName variable.

    Here's the function:

    CREATE FUNCTION [dbo].[RS_NoName] (@cID char(9))
    RETURNS bit AS
    BEGIN
    declare @Res as bit, @cName as char(50)

    select @Res = null
    select @cName = f.name
    from tblFirst f (nolock) inner join tblSecond s (nolock)
    on (f.linkid=s.linkid)
    where f.ID = @cID

    if @cName is null or len(ltrim(rtrim(@cName)))=0
    begin
    select @Res=1
    end
    RETURN @Res
    END
    Where linkid is the field that records in both tables are corresponding by.

    Therefore instead of getting a list of customers where tblFirst.name is null, i also get all customers which do not have corresponding records in tblSecond, regardless of their rblFirst.name value...

    Can anyone please help?
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

  2. #2
    Join Date
    Nov 2003
    Location
    Riga, Latvia
    Posts
    36

    I am stupid, though figured it out ;)

    Since I'm declaring variable @cName and then checking it against null for even those records which exist only in tblFirst and not in tblSecond, and, of course, selecting from join returns null @cName value...

    Will have to rework it with if exists... etc.

    So, question closed.
    Replies based on my configuration: Windows XP / Microsoft Office Access 2003 unless specified otherwise.

Posting Permissions

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