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

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..."

So how do I do the JOIN to be able to reference f in the WHERE?

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.

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.

I hope you don't have more than 10K records in your tables, otherwise it'll take forever. :-)

n_i, beautiful solution

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.

There are spelling errors in your version of the query:Would you mind posting your table definitions?

I understand your irony :-)

what irony?

not many people know about that alternative to an outer join

it was a sincere compliment

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

Yeah, you're right. Try LOCATE function instead of POSSTR - that should work.

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?

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.