Syntax issue: URGENT

[denero_watz]

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

Thanks in advance,
DW

[4.spam]

Hello.

select Table1.id
from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

Thanks in advance,
DW

[Serge Rielau]

de*********@yahoo.com wrote:

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

There is no pretty way to do this.
The migration toolkit provides a Java function with a generalized LIKE.
You can also get nasty:
CREATE PROCEDURE INSTR_P(IN string VARCHAR(128),
IN substring VARCHAR(128),
OUT found CHAR(1))
DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION
SET found = CASE WHEN string LIKE '%' || substring || '%'
THEN 1 ELSE 0 END;

--#SET TERMINATOR @
CREATE FUNCTION INSTR(string VARCHAR(128),
substring VARCHAR (128))
RETURNS CHAR(1)
DETERMINISTIC CONTAINS SQL NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE result CHAR(1);
CALL INSTR_P(string, substring, result);
RETURN result;
END
@
--#SET TERMINATOR ;
SELECT
Table1.id from Table1, Table2
where INSTR(Table2.longname, Table1.name) = 1

I didn't test it...

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

de*********@yahoo.com wrote:

Hello,

I have a requirement where I need to return all the ids from Table1 if
the column 'name' of Table1 is in (i.e. a substring of) the 'longname'
column of Table2. How do I correct my SQL query as the below one is
showing error:

select Table1.id from Table1, Table2 where (Table2.longname LIKE
'%'||Table1.name||'%')

LIKE requires a constant string (either explicitly specified or originating
from a host variable) as pattern. This is not the case here because your
pattern in dynamic.

You can use the functions POSSTR or LOCATE for the test. Serge offered
another solution. Yet another approach is to use regular expression
matching as is described here:
http://www.ibm.com/developerworks/db...301stolze.html

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Tonkuma]

select Table1.id

from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;

I think POSSTR also have similar restriction for search string as LIKE.
So, you should use LOCATE.

select Table1.id
from Table1, Table2
where LOCATE(Table1.name, Table2.longname) 0;

[4.spam]

Yes, that's true.
I tested this at my iSeries V5R3.
posstr in iSeries supports string expression (including column names)
in 2-nd argument...

select Table1.id
from Table1, Table2
where posstr(Table2.longname, Table1.name)!=0;

I think POSSTR also have similar restriction for search string as LIKE.
So, you should use LOCATE.

select Table1.id
from Table1, Table2
where LOCATE(Table1.name, Table2.longname) 0;

[denero_watz]

Dear All:
Thanks for all the responses.
DW