Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: Help with a Query

    The following query is used to search for a name in the database. The name is dynamically entered via a web form. Each name can have multiple ways of spelling or ending so it does a like search, but I want the result to only return one matching value.

    EXAMPLE..

    search for abc can return
    - abc limited
    - abc lim
    - abc lim.


    SELECT r.Id, r.IM_OrgId, im.Name, r.BK_OrgId, bk.Name
    FROM Rule r
    INNER JOIN OrgName im ON im.Org_Id = r.IM_OrgId
    INNER JOIN OrgName bk ON bk.Org_Id = r.BK_OrgId AND bk.isDefault = 1
    WHERE im.Name LIKE UPPER('abc%')

    The isdefault flag limits the bk.Name to only a single result on the join, but im.name can have many values returned...

    RESULTS

    123, 321.23, abc limited, 342, Test
    123, 321.23, abc lim, 342, Test
    123, 321.23, abc lim., 342, Test
    432, 324.23, abc 123, 432, Test2

    The above results need to only list the 2 ids, 123, 432....which im.name that gets displayed does not matter.


    Any help would be great..

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Try adding 'fetch first 1 row only' to the end of your query.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    http://dbforums.com/showthread.php?threadid=921672

    This post should give you a clue


    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    I tried playing with these examples but nothing seemed to work (i was never able to filter out duplicate aliases)....any other ideas?

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I think what Sathyaram was trying to get you to do was to look at the row_number() function to do what you want. Try something like:

    with t1 (id, rownum, im_orgid,IM_name,bk_ordid,BK_NAME) as
    (SELECT r.Id, row_number() over (partition by r.id),r.IM_OrgId, im.Name, r.BK_OrgId, bk.Name
    FROM Rule r
    INNER JOIN OrgName im ON im.Org_Id = r.IM_OrgId
    INNER JOIN OrgName bk ON bk.Org_Id = r.BK_OrgId AND bk.isDefault = 1
    WHERE im.Name LIKE UPPER('abc%')) select id,im_orgid,IM_name,bk_ordid,BK_NAME from t1 where rownum = 1

    HTH

    Andy

Posting Permissions

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