RE: Need SQL help -- MIDRANGE-L

... AFAIK not only 7.3 but also a special PTF

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of VERNON HAMBERG Owner via MIDRANGE-L
Sent: Wednesday, 11 December 2024 16:52
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>; midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: Need SQL help

The function is REGEXP_INSTR - this requires at least 7.3, as well.

Good luck!
Vern

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

To: midrange systems technical discussion

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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto:MIDRANGE-L@lists.midrange.
com> To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request
@lists.midrange.com> Before posting, please take a moment to
review the archives at https://archive.midrange.com/midrange-l.

Please contact
support@xxxxxxxxxxxxxxxxxxxx<mailto:support@xxxxxxxxxxxxxxxxxxx
m> for any subscription

related

questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
mailing list To post a message email:
MIDRANGE-L@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L@xxxxxxxxxxxxxxxxx
m> To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email:
MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx<mailto:MIDRANGE-L-request@l
ists.midrange.com> Before posting, please take a moment to review
the archives at https://archive.midrange.com/midrange-l.

Please contact
support@xxxxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:MIDRANGE-L-request@list
s.midrange.com> Before posting, please take a moment to review the
archives at https://archive.midrange.com/midrange-l.

Please contact
support@xxxxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto:MIDRANGE-L-request@lists.
midrange.com> Before posting, please take a moment to review the
archives at https://archive.midrange.com/midrange-l.

Please contact
support@xxxxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto: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<mailto: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.