RE: joining 3 files -- RPG400-L

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