Re: Need SQL help

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Actually, with only 100 rows in table2, the more performant solution might
be REGEXP_INSTR

select *
from mytable1
where regex_instr(myfield1 , '123456|345678|...') > 0

Building the regex pattern could be done manually
select listagg(trim(flda),'|')
from mytable2

Or you could I think swap the above select into the expression...
select *
from mytable1
where regex_instr(myfield1,
                  select listagg(trim(flda),'|') from mytable2
                   )> 0

HTH,
Charles



On Tue, Dec 10, 2024 at 4:22 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> Are there any other fields in MYTABLE?  Particularly a unique key?
>
> with unpivot as (
>   select key, varchar(element, 15) as f1
>    from mytable, table(systools.split(myfield1, ';'))
> )
> select *
> from mytable1 t1
> where t1.key in (select u.key from mytable2 t2 inner join unpivot u on
> u.f1 = m2.flda)
>
> If there's no unique key, you could use RRN.
>
> Generally, you don't want to create a temporary table unless you have
> multiple processes running over it.
> Yes, this will take more memory, but usually less time.
>
> HTH,
> Charles
>
>
>
> On Tue, Dec 10, 2024 at 12:25 PM Vinay Gavankar <vinaygav@xxxxxxxxx>
> 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.
> >
> > TIA
> > Vinay
> > --
> > This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> > list
> > To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> > or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at https://archive.midrange.com/midrange-l.
> >
> > Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
> > questions.