|
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.concat('%',
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
charles.wilt@xxxxxxxxx>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 <
inwrote:
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
Theywrote:
But SQL scripts are not permitted in our Production environment.
alluse
sql interface SEQUEL software (by fortra) , which does not allow
ofdoing
the
commands in standard sql. So if someone has any suggestions for
this
with a single sql statement, I can try to see if it can be done
vinaygav@xxxxxxxxxSEQUEL.
Vinay
On Tue, Dec 10, 2024 at 7:52 PM Vinay Gavankar <
yououtput I
wrote:
There is no unique key.
When I said "temporary" I actually meant that is the final
key?charles.wilt@xxxxxxxxxwant
in the table.
Vinay
On Tue, Dec 10, 2024 at 6:22 PM Charles Wilt <
wrote:
Are there any other fields in MYTABLE? Particularly a unique
unpivot
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
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
valueshavevinaygav@xxxxxxxxx
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 <
FLD1
wrote:
I have a Table (MYTABLE1) with millions of rows with a field
with a(256
chars). I have another table (MYTABLE2) with about 100 rows
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
(MIDRANGE-L)of
example:all
the
records in MYTABLE2.
I know I haven't explained it properly so let me give an
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
https://lists.midrange.com/mailman/listinfo/midrange-lmailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
mailingsubscriptionsubscriptionor 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
mailingrelated
questions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
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
mailingmailingrelated
--questions.
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
listrelated
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
questions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
listrelated
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
questions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)
relatedlistrelated
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
listquestions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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
listquestions.--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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 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.
midrange.com
