RE: Need SQL help

Hmmm -

Since LOCATE_IN_STRING can only handle one value at a time, this search would require recursive query which would process the MYTABLE2 file one row w at a time for each row in MYTABLE1 in order to deal with the limitations of the LOCATE_IN_STRING scalar function.

It is too late in the day and I'm having a brain fart trying to figure out the syntax of the necessary recursive query.

Surely one of the SQL geniuses here can figure this out!

Regards,
Steve

Vinay wrote:
No. I think the SELECT FLDA FROM MYTABLE2 is returning multiple values
and the locate_in_string expects only a single value for the second parm.

Rob wrote:
You're not doing a SELECT INTO, are you?

Vinay wrote:
I get "Result of SELECT more than one row" error when I try to run
this

SAM_L wrote:

SELECT * FROM MYTABLE1 WHERE
LOCATE_IN_STRING(
FIELD1,
(SELECT FLDA FROM MYTABLE2)
) > 0

Vinay wrote:
I have a Table (MYTABLE1) with millions of rows with a field
FLD1 (256 chars). I have another table (MYTABLE2) with about 100 rows with
a field FLDA (15 chars).

I want to get all records from MYTABLE1 where FLD1 has FLDA.
If FLDA has a value of 'ABCD' then Select * from MYTABLE1 where
FLD1 like %ABCD% would probably do the job. But I need to do it
for FLDA values of all the records in MYTABLE2.

I know I haven't explained it properly so let me give an example:
FIELD1 in MYTABLE1:
Row 1 - ABCDEF;123456;GH;78
Row 2 - CDEFG;345678;
Row 3 - 345678;EWRT;9888
Row 4 - 85465;ASDFGT;QWERTY;85656

FLDA in MYTABLE2:
Row 1 - 123456
Row 2 - 345678

The final sql should return Rows 1,2,3 from MYTABLE1.