Re: Need SQL help

try casting it to a varchar
select *
from mytable1
where regexp_instr(myfield1,
select cast(listagg(trim(flda),'|') as varchar(2000))
from mytable2
)> 0

On Wed, Dec 11, 2024 at 8:51 AM Vinay Gavankar <vinaygav@xxxxxxxxx> wrote:

select *
from mytable1
where regexp_instr(myfield1,
select listagg(trim(flda),'|') from mytable2
)> 0

gives error "Argument 02 of function regexp_instr not valid"

On Wed, Dec 11, 2024 at 10:37 AM Vinay Gavankar <vinaygav@xxxxxxxxx>
wrote:

I ran this on the development box:
select *
from mytable1
where regex_instr(myfield1,
select listagg(trim(flda),'|') from mytable2
)> 0

I got error "REGEX_INSTR in *LIBL type *N not found"

On Wed, Dec 11, 2024 at 10:06 AM Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

I gave you two different single SQL statements...

But if you can actually use SQL, I don't know why you'd ask for it.

Charles

On Wed, Dec 11, 2024 at 8:04 AM Vinay Gavankar <vinaygav@xxxxxxxxx>
wrote:

But SQL scripts are not permitted in our Production environment. They

use

sql interface SEQUEL software (by fortra) , which does not allow all

of

the

commands in standard sql. So if someone has any suggestions for doing

this

with a single sql statement, I can try to see if it can be done in

SEQUEL.

Vinay

On Tue, Dec 10, 2024 at 7:52 PM Vinay Gavankar <vinaygav@xxxxxxxxx>

wrote:

There is no unique key.

When I said "temporary" I actually meant that is the final output I

want

in the table.

Vinay

On Tue, Dec 10, 2024 at 6: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.

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

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

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

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