Re: Need SQL help

I don't see it in the IBM documentation, but apparently the REGEXP_INSTR scalar function
does not allow a SQL statement to be used as the pattern-expression.

However, it does allow a SQL Variable. This works for me:

DROP TABLE QTEMP.FINDTEXT;
DROP VARIABLE QGPL.FINDTEXT;
DROP TABLE QTEMP.FOUNDITEMS;

CREATE TABLE QTEMP.FINDTEXT
(TEXTFIND CHAR (50 ) NOT NULL WITH DEFAULT);

INSERT INTO QTEMP.FINDTEXT
VALUES ('IRON'),('ALUMINUM'),('STEEL'),('TITANIUM');

CREATE VARIABLE QGPL.FINDTEXT CHAR(50)
DEFAULT( SELECT LISTAGG(TRIM(TEXTFIND),'|') FROM QTEMP.FINDTEXT );

CREATE TABLE QTEMP.FOUNDITEMS (LITM,DSC1) AS
(
SELECT IMLITM,IMDSC1
FROM F4101
WHERE REGEXP_INSTR(IMDSC1 , QGPL.FINDTEXT) > 0
) WITH DATA;

SELECT * FROM QTEMP.FOUNDITEMS

2nd Item Description
Number
-------------------- --------------------
42900018 LATCH STEEL SLD KIT *
3869100 PAINT ALUMINUM RUST RESIST
1088004 STEEL BALL WV4500/WV4600
6009020 COIL 13 STAINLESS STEEL X 36"
3669050 SQUARE 1" HR x 20' A36 STEEL
1488029 CHAIN #35 STEEL SASH 9 X 10-0
1328123 BOLT FORGED STEEL EYE 5/8 X 6
1469010 COIL 0.072 ALUMINUM X 48"

Regards,
Steve Landess
(512) 289-0387

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Vinay Gavankar <vinaygav@xxxxxxxxx>
Sent: Wednesday, December 11, 2024 10:24
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Need SQL help

I get the same error even with cast.

I tried it with building the pattern separately using listagg (with just 3
records in table2) and putting it manually as the second parm of
regexp_instr. That works.

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

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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907558022%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=5zF3hRIgnSKYINyObNRJ0i8n4LnMNaHKRsDykY410sE%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907591659%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=ALW29CHCR1gBfiqg4P%2BfevT94Gnz2bePgbYcDCke1Rw%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907606569%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Zmtm%2FkeukmHagd94ZAFqltqkKyl5yAYwYyrHv7hqMRI%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907618576%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=QJjRX6UFTOo8R0UiwgZ2k%2FEHzPHA9e7c29ys51KInNg%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907630709%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Le7P4HuwI6vsHhsGBsMSksa1HdWquvfrlMTTpNqYIzg%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907643135%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=I5HdX2aibUAt9lnRuZgjyHoYwuEDohIIsN8JX5x9%2BrU%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907654807%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=0c%2B72PZWZu4TtImVQCzIcBg3LRUyeqHEjkY%2FtyyjqS4%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907666408%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=eHGAr9dlQ9N5uExvdwBW7sxy9tLmmLL2btqqryfr5%2FI%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907678481%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=z%2Blqc6OgsxHeS3fY8OcVUmPEUecDKFOoSQ4ElTtHQPE%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907689996%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=QBZWJdUR1Mqct2MmqmmocM0X9NNoas65PZglU543UvY%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907702864%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=ZU9h%2B%2BvI6xb%2Bjw8soywCUqIpJS%2FSyLWM%2B41zm4hyq9I%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907714140%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=4kQvr%2BCkkUtkfdzpVKXlZHvJw91EcuNyPmfUYDQlJUc%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907729810%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=knW5bN6aw6Uyduu8Q8yehXA0pb7qJ%2FtS7eELRzKhqZw%3D&reserved=0<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://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&data=05%7C02%7C%7Cfb5ba7dd9153419f69d108dd1a0053ec%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638695310907742556%7CUnknown%7CTWFpbGZsb3d8eyJFbXB0eU1hcGkiOnRydWUsIlYiOiIwLjAuMDAwMCIsIlAiOiJXaW4zMiIsIkFOIjoiTWFpbCIsIldUIjoyfQ%3D%3D%7C0%7C%7C%7C&sdata=Y6kAwLipGpv%2F%2BVPrCsFp%2B5V6Ab9YJlOYKD0%2B5ylSa74%3D&reserved=0<https://archive.midrange.com/midrange-l>.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.