1.  Home
  2. MIDRANGE-L
  3. December 2024

Re: Need SQL help

You're right, seems.

Maybe is safer (myfield1 like concat('%;', concat(trim(flda), '%')) or
myfield1 like concat('%', concat(trim(flda), ';%')))?

unless myfield1 contains only one element.

HTH
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno mer 11 dic 2024 alle ore 17:37 Vinay Gavankar <vinaygav@xxxxxxxxx>
ha scritto:

Thanks Marco. That seems to work.

Vinay

On Wed, Dec 11, 2024 at 11:24 AM Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

select myfield1 from mytable2 join mytable1 on myfield1 like concat('%',
concat(trim(flda), '%'));

HTH
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco


Il giorno mer 11 dic 2024 alle ore 16:37 Vinay Gavankar <
vinaygav@xxxxxxxxx>
ha scritto:

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.


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



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.