× 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.



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


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.