Re: joining 3 files -- RPG400-L

this CREATE VIEW QTEMP/FILE3
( FLD1 , FLD2 , FLD3 , FLD4 ) AS
( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
FROM QTEMP/FILE1 a
JOIN QTEMP/FILE2 b
ON A.FLD1 = B.FLD1
);

is resulting in Select or omit error on field cast(translate(file2.FLD1)

Also i had mentioned it clearly that fld1 in file2 is of character data
type field with length 9 which may have that 6 digit number (consecutive 6
digit number) at any position ( if total length of this field is 9 then it
must be between 1st to 4th position only this number length is 6 only)

Also I am just testing it without creating views for file1 and file 2 ( and
directly using these files but the main requirement is to get matching data
of these files for the mentioned fields.

for below suggested join-

CREATE VIEW QTEMP/FILE3

( FLD1 , FLD2 , FLD3 , FLD4 ) AS
( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
FROM QTEMP/FILE1 a
JOIN QTEMP/FILE2 b
ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))
)

I did get the matching records for my files but as i said earlier like this
:-

FLD1 FLD1
1234 001234
1234 001234XY

So this extra XY which is coming this is just an example i have multiple
such different set of records which come when i execute that select * from
qtemp/file3 ( or file3 created in my library)

FLD1 ( FIRST FILE FIELD OF 6 LENGTH DECIMAL TYPE)
FLD1(SECOND FILE FIELD OF CHARACTER TYPE WITH 9 LENGTH)

345
000345
345
000345JK

So these extra two characters coming as a second record pair is the only
problem appears with this join.

Thanks.

Thanks

On Thu, 23 Jun 2022 at 21:58, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Hi,

the problem is, you are comparing apples with pears!
DIGITS will keep all numbers an replace leading blanks with *Zeros and
removes the decimal separator as well as the minus sign, i.e. you will have
digits only
CHAR will left adjust the numbers (i.e. leading blanks are removed) and
keeps the decimal separator as well as the minus sign.

BTW SQL is smart enough to do the conversion directly, i.e. if you just
join
your samples tables as follows, your SQL Query will be performed correctly.
Select *
from QTEMP.File1 a join QTEMP.File2 b on a.Fld1 = b.Fld1;

If you create your view as follows, it will be created and when executing
it, it should return the expected result:
CREATE VIEW QTEMP/FILE3
( FLD1 , FLD2 , FLD3 , FLD4 ) AS
( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
FROM QTEMP/FILE1 a
JOIN QTEMP/FILE2 b
ON A.FLD1 = B.FLD1
);

SELECT * FROM QTEMP/FILE3
Will return the following values:
111111 File 1 rec 1 111111 File 2 rec 1
222222 File 1 rec 2 222222 File 2 rec 2
333333 File 1 rec 3 333333 File 2 rec 3
444444 File 1 rec 4 444444 File 2 rec 4
111111 File 1 rec 1 111111 File 2 rec 5
222222 File 1 rec 2 222222 File 2 rec 6

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i

"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)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of jerry
ven
Sent: Donnerstag, 23. Juni 2022 13:39
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: joining 3 files

when i ran this view file3 it showed matching records of FLD1 of file 2 (
which is of 9 length and character data type and where that 6 digit
number(
consecutive 6 digits) is present at random location ) like these records
for
example in my data i got it like 111111xy ,i mean for some of the fields
it's showing correctly up to exactly matching length of 6 but for some of
the fields i am getting two extra characters like 111111xy.
So how to get that exact 6 digit number from file2 for FLD1 which should
match FLD1 of file1 here so that in result of Select * from QTEMP/FILE3 i
should not see those extra characters in FLD1 of file2 here.

Thanks

On Thu, 23 Jun 2022 at 02:13, Stephen Landess <steve_landess@xxxxxxxxxxx>
wrote:

DROP TABLE QTEMP/FILE1;
DROP TABLE QTEMP/FILE2;
DROP VIEW QTEMP/FILE3;

CREATE TABLE QTEMP/FILE1
( FLD1 NUMERIC (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' )
, ( 444444 , 'File 1 rec 4' )

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' )
, ( ' 222222 ' , 'File 2 rec 2' )
, ( ' 333333 ' , 'File 2 rec 3' )
, ( ' 444444' , 'File 2 rec 4' )
, ( ' 111111 ' , 'File 2 rec 5' )
, ( ' 222222 ' , 'File 2 rec 6' )
;

CREATE VIEW QTEMP/FILE3
( FLD1 , FLD2 , FLD3 , FLD4 ) AS
( SELECT A.FLD1 , A.FLD2 , B.FLD1 , B.FLD2
FROM QTEMP/FILE1 a
JOIN QTEMP/FILE2 b
ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))
)

Select * from QTEMP/FILE3

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link:
https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com