Jerry,
You've spent a lot of your time and people's time on this question.
That's largely because you haven't clearly and succinctly spelled out
the requirements, though you have given a lot (of hard to read)
examples.
As I understand it:
You have 9-character field in it somewhere is a string of 6 digits.
You want to find those digits and return a packed 6 digit number.
The string must be exactly 6, no less and no more.
There can be other characters before or after the 6 digits.
The may be some shorter or longer string of digits.
So here's an SQL solution.
-----------------------------------------
First, it creates some test data. it would have saved time
all round if you had provided something like this first.
DROP TABLE QTEMP.FILE1;
DROP TABLE QTEMP.FILE2;
DROP VIEW QTEMP.FILE3;
CREATE TABLE QTEMP.FILE1
( FLD1 decimal (6 , 0) NOT NULL WITH DEFAULT
, FLD2 CHAR (20) NOT NULL WITH DEFAULT
)
;
Insert into QTEMP.FILE1
VALUES
( 111111 , 'File 1 rec 1' )
, ( 222222 , 'File 1 rec 2' )
, ( 333333 , 'File 1 rec 3' )
, ( 123456 , 'File 1 rec 4' )
, ( 987654 , 'File 1 rec 5' )
, ( 234 , 'File 1 rec 6' )
;
CREATE TABLE QTEMP.FILE2
( FLD1 CHAR (09) NOT NULL WITH DEFAULT
, FLD2 CHAR (20) NOT NULL WITH DEFAULT
)
;
Insert into QTEMP.FILE2
VALUES
( '111111 ' , 'File 2 rec 1 YES' )
, ( '123456 ' , 'File 2 rec 2 YES' )
, ( 'a444444bc' , 'File 2 rec 3 YES' )
, ( ' 987654 Z' , 'File 2 rec 4 YES' )
, ( ' 123456' , 'File 2 rec 5 YES' )
, ( ' 000234' , 'File 2 rec 6 YES' )
, ( '12345 ' , 'File 2 rec 7 NO' )
, ( 'ab 22222z ' , 'File 2 rec 8 NO' )
, ( ' 12345678' , 'File 2 rec 9 NO' )
, ( '123456789' , 'File 2 rec 10' )
;
----------------------------------------
Now we are going to create a view over FILE2, which
contains an extra column "theNum" which is the
extracted number, if these is one.
It uses common table expressions to simplify the steps.
First, it uses Regex to dig out the number string. I'm not
a regex guru, but this is relatively simple in that it
finds an string of digits that is 6 to 9 characters long.
(There may be a better regex that can do this all in
one step, but I couldn't figure it out.)
The it takes non-null results the are 6 characters long
and converts them to decimal 6 in a new field "the Num"
and creates a view QTEMP.FILE3.
-----------------------------------------
create or replace view qtemp.file3 as
with file2regex as
(select
regexp_substr(fld1,'\d{6,9}') as theStr,
a.*
from qtemp.file2 a
)
,
file2nums as
(select
decimal(theStr,6,0) as theNum,
a.fld2, a.fld1
from file2regex a
where length(theStr) = 6 and theStr is not null
)
select * from file2nums;
-----------------------------------------
-- show contents of the view
select * from qtemp.file3;
-----------------------------------------
Now you can joins FILE3 back to FILE1.
select * from qtemp.file1 a, qtemp.file3 b
where a.fld1 = b.theNum;
If this doesn't do exactly what you want you should be able to tweak it.
Caveat: May or may not perform and you didn't tell us if it was a one
time job or how big the files are.
On 6/29/2022 1:38 PM, jerry ven wrote:
Hi,
there won't be any trailing or leading blanks or any leading/trailing
characters :-
If there is any non numeric value in the first 6 positions in the Field2 of
file2 then that record would not be considered.
A good join example would be like below :-
midrange.com
