Problem with comparing fields in 2 table with a LIKE clause

[sriki]

hi,
Iam running into a problem.

I have 2 tables Table1 and Table2. I want to take a string field A.F2 from A and find records in B where B.F2 like A.F2. The join is on a field called F1 in the two tables. All fields in the 2 tables are of the same varchar datatype.

The sql goes something like:
SELECT B.F2 FROM Table1 A,Table2 B WHERE A.F1=B.F1 AND B.F2 LIKE A.F2

On running the sql, I get the followsing error
DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. : [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

Is there a way to get the behavior I want through alternate constructs in DB2 SQL?
thanks,
Sri

 

[Dagon]

It doesn't look as if LIKE or POSSTR allow column names as the second argument. You could try:

SELECT B.F2 FROM Table1 A,Table2 B
WHERE A.F1=B.F1 AND SUBSTR(B.F2,1,LENGTH(A.F2)) = A.F2