trimming field value

[MFisher]

To aid those trying to help, please provide:

1) CREATE TABLE scripts for File1 & File2, that others can copy-paste into their test area.
2) INSERT statements for File1 & File2, with sample data of the various combinations of data expected to be encountered, that others can copy-paste into their test area.
3) A sample result set of original data and expected results .

[John192]

Hi,

For more clarification please find the details attached in the notepad file.



Thanks

[John192]

...just to correct for expected outcome( for fine records) for the attached notepad file how the matching records from file2's field2 should be displayed when the SQL join query should be executed:-

Fld1 Fld2
123456 123456
1 000001
123 000123
1234 001234
1234 001234
12 000012
123456 123456
123456 123456



Thanks

[John192]

it got misaligned so attaching notepad file for the expected output for records matching for fine criteria here for my post #47.



Thanks

[Vectorspace]

You have ">" in various places in your example,m whicb I assume are copy paste errors and should actually be newlines
In which case I assume your statement is actually 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 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))

In which case, you are missing a closing bracket - you need an extra ')' at the very end

[Vectorspace]

Please disregard the above, I missed that there were multiple pages in this thread

[John192]

Ya, that error putting ') ' and ">" I had corrected but unable to form a query which could provide me the desired matching records like I mentioned in my posts #47, 47.1,48.


Thanks.

[MFisher]

Rather than attachments which are a security risk, copy-paste as CODE.

[ftrento]

SUBSTR(field1,1,6)

Just to mention that you can use also the LEFT function that explicitly does this (gettin a prefix)

[MFisher]

This is what I mean about providing CREATE & INSERT statements.

CREATE OR REPLACE TABLE QTEMP.FILE_1 ( FIELD1 NUMERIC(6,0) NOT NULL ) ON REPLACE DELETE ROWS RCDFMT FILE_1 ;
CREATE OR REPLACE TABLE QTEMP.FILE_2 ( FIELD2 VARCHAR(9) NOT NULL ) ON REPLACE DELETE ROWS RCDFMT FILE_2 ;
INSERT INTO QTEMP.FILE_1 SELECT 111 from SYSIBM.SYSDUMMY1 ;
INSERT INTO QTEMP.FILE_1 SELECT 222 from SYSIBM.SYSDUMMY1 ;
INSERT INTO QTEMP.FILE_1 SELECT 333 from SYSIBM.SYSDUMMY1 ;