Results 1 to 15 of 15
  1. #1
    Join Date
    Aug 2003
    Posts
    58

    Unanswered: How do I join a table using LIKE in the on clause with DB2

    Is it possible to join two tables with a LIKE rather than an =?

    select n.nodename, f.searchid
    from mstresrc.nodes as n
    left outer join mstrdata.foo as f on f.searchword like n.nodename

    I get:

    SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the
    first operand is not a string expression or the second operand is not a
    string. SQLSTATE=42824

    nodename is a VARCHAR, foo is below:

    select * from mstrdata.foo

    SEARCHID SEARCHWORD
    ----------- ------------------------
    100 %cobi%

    1 record(s) selected.


    Is the only not possible in DB2? Is there another way of storing
    a search string in the DB that I can use to find data in another table?

    Thanks,
    Wayne

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I don't believe you can specify a column name on the right side of LIKE.

    How about "...WHERE POSSTR(n.nodename, f.searchword) > 0..."

  3. #3
    Join Date
    Aug 2003
    Posts
    58
    So how do I do the JOIN to be able to reference f in the WHERE?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If there is some application program interface you are using (or even stored procedure) you can read the column into a program variable and then include the variable in a subsequent SQL statement with the LIKE.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Aug 2003
    Posts
    58
    Yes, I was aware of a solution like that but that could cause very long SQL statements (is there a limit on how many chars can be in a statement?)
    and may also cause performance issues.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by wayneb64
    So how do I do the JOIN to be able to reference f in the WHERE?
    Code:
    select .. from mstresrc.nodes as n, mstrdata.foo f
    where POSSTR(n.nodename, f.searchword) > 0
    union all
    select .. from mstresrc.nodes as n1 where not exists
    (select 1 from mstrdata.foo where POSSTR(n1.nodename, searchword) > 0)
    I hope you don't have more than 10K records in your tables, otherwise it'll take forever. :-)

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    n_i, beautiful solution

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Aug 2003
    Posts
    58
    I am sorry, my SQL syntax knowledge is not good enough....

    select n.nodename, f.searchid
    from mstresrc.nodes as n, mstrdata.foo as f
    where posstr( f.searchword, n.nodename) > 0
    union all
    select n.nodename from mstresrc.node as n1
    where not exixts
    (select 1 from mstrdata.foo where posstr(n1.nodename, searchword) > 0)

    produces a syntax error in one tool and gives no output on the DB2 command
    line...


    select n.nodename, f.searchid
    from mstresrc.nodes as n, mstrdata.foo as f
    where posstr( f.searchword, n.nodename) > 0

    ... causes the same error as when trying it in the JOIN. It wants a string not a field.

  9. #9
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There are spelling errors in your version of the query:
    Quote Originally Posted by wayneb64
    select n.nodename, f.searchid
    from mstresrc.nodes as n, mstrdata.foo as f
    where posstr( f.searchword, n.nodename) > 0
    union all
    select n1.nodename from mstresrc.node as n1
    where not exists
    (select 1 from mstrdata.foo where posstr(n1.nodename, searchword) > 0)
    Quote Originally Posted by wayneb64
    select n.nodename, f.searchid
    from mstresrc.nodes as n, mstrdata.foo as f
    where posstr( f.searchword, n.nodename) > 0

    ... causes the same error as when trying it in the JOIN. It wants a string not a field.
    Would you mind posting your table definitions?

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by r937
    n_i, beautiful solution

    I understand your irony :-)

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by n_i
    I understand your irony :-)
    what irony?

    not many people know about that alternative to an outer join

    it was a sincere compliment
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Aug 2003
    Posts
    58
    I'm sorry, I dont see the spelling error. Here are the table definitions,
    queries, and there errors:


    CREATE TABLE MSTRESRC.NODES
    (NODEID INTEGER NOT NULL,
    NODENAME VARCHAR(24) NOT NULL,
    MACHID INTEGER NOT NULL,
    NODESTATUS SMALLINT NOT NULL,
    TEAMID SMALLINT,
    NODECLASS SMALLINT NOT NULL,
    NODEOWNER INTEGER);

    create table mstrdata.foo
    (searchid int not null,
    searchword varchar(24) not null)


    select n.nodename, f.searchid
    from mstresrc.nodes as n,
    mstrdata.foo as f
    where posstr(f.searchword, n.nodename) > 0

    [IBM][CLI Driver][DB2/NT] SQL0132N A LIKE predicate or
    POSSTR scalar function is not valid because the first
    operand is not a string expression or the second operand is
    not a string. SQLSTATE=42824

    select n.nodename, f.searchid
    from mstresrc.nodes as n, mstrdata.foo as f
    where posstr( f.searchword, n.nodename) > 0
    union all
    select n1.nodename from mstresrc.node as n1
    where not exists
    (select 1 from mstrdata.foo where posstr(n1.nodename, searchword) > 0)

    An error occurred while running the query.

    [IBM][CLI Driver][DB2/NT] SQL0132N A LIKE predicate or POSSTR scalar function is not valid because the first operand is not a string expression or the second operand is not a string. SQLSTATE=42824

  13. #13
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Yeah, you're right. Try LOCATE function instead of POSSTR - that should work.

  14. #14
    Join Date
    Aug 2003
    Posts
    58
    Well LOCATE does not cause a DB2 error, but its not treating the '%' characters as wildcards so its not really doing what I need. Why wont DB2 compare two string
    fields with LIKE or POSSTR?

  15. #15
    Join Date
    Aug 2003
    Posts
    58
    So, the only format I can get to work is this:

    select n.nodename
    from mstresrc.nodes as n
    where (n.nodename like '%cobi%' OR n.nodename like 'hmc%' OR n.nodename like 'abba')

    which will finds the nodes which have a matching search word but wont tell
    me what the searchid was that found it. How far will this scale? If I have
    200 searchwords, do I need to run 200 queries to find match the search
    ids with the nodes?

    This is a bad example as I dont really want to associate search ids with nodes, what I really want is to associate comments with a searchword,
    and then when I show a list of nodes, show any comments that match.
    The example just lets me figure out the best way to do this using DB2.

Posting Permissions

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