× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.