Copy-paste from your JOIN, if that is what you want.
Announcement
Collapse
No announcement yet.
trimming field value
Collapse
X
-
-
I tried this:-
CREATE VIEW mylib/FILE3 ( FLD1 , fld2 ) AS ( SELECT A.FLD1 , B.FLD2) FROM LIBa/FILE1 a JOIN LIBb/FILE2 b ON
A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD2, '[^0-9]',''),'')))
and then ran select * from mylib/file3
then getting results like ( Just like mentioned in my post #20) :-
FLD1 FLD2
1 001 F
23 ABCDE001
2,425 AB002525
which is not acceptable as per acceptance criteria mentioned in my POST #25
Thanks
Comment
-
-
-
-
Originally posted by Vectorspace View PostThe IBM i 7.4 SQL documentation is here: https://www.ibm.com/docs/en/i/7.4?topic=reference-sql
Firstly, this line:
ON DIGITS(A.FLD1) = CAST(TRIM(B.FLD1) AS CHAR(6))
You should avoid doing a transform on both sides of the equals in a logical comparison in SQL, as it means the SQL engine cannot use keyed lookups and must check every record in the table(s). In your example, you should convert B.FLD1 to numeric so it can be directly compared to A.FLD1 so A.FLD1 does not need to be transformed. Then FILE1 can use keyed lookups if an index is available.
Secondly, it doesn't seem like you want to trim XY form the end, since as you said, the numeric values can appear anywhere inside the field. So you could have X001234XY ?
What you actually want is to extract the numeric portion of the field. If you can guarantee that there is only a single block of numbers (i.e. not X12B34C where you only want the 12, not 1234), then you can use REGEXP_REPLACE: https://www.ibm.com/docs/en/i/7.4?to...regexp-replace
REGEXP_REPLACE lets you find and replace characters in a string using Regex pattern matching. This will take the string B.FLD1, and replace all characters that are not numbers (0-9) with an empty string, removing them. Then you can wrap that in INT() to convert to a numeric that can be directly compared with A.FLD1:
on A.FLD1 = INT(REGEXP_REPLACE(B.FLD1,'[^0-9]',''))
Note this only works if you can guarantee that every B.FLD1 contains at least one numeric character. If not, REGEXP_REPLACE returns an empty string, and int() will error trying to convert it. To solve that, you can wrap REGEXP_REPLACE with NULLIF() https://www.ibm.com/docs/en/i/7.4?to...nctions-nullif
NULLIF(a,b) returns a if a is not equal to b, else it returns null. So you can convert as blank return from REGEXP_REPLACE to null, which is passed through INT(), resulting in the comparison safely returning false
on A.FLD1 = INT(NULLIF(REGEXP_REPLACE(B.FLD1,'[^0-9]',''),''))
fld1 & fld2 then i should be able to display corresponding matching records against fld1 ( whose data type is decimal and length is 6) in to fld2 ( example if fld1 has value 123456 and fld2 has value
000123456 then this is a match , if fld2 has a value like **123456* then it's not a match ( here * means any nonnumeric value could be any thing even blanks or space also) also just for example if fld1 has a value 123 and fld2 has a value '00 123 ' ( here space or blanks i have added just for explanation before123 2 blanks and after 123 also 2 blanks ) then it will not be a match also
fld1 being of length 6 and data type decimal(packed) could even have a value of single digit also like value '1' then if fld2 has a value '000000001' then it would be a match.
so space or blanks in fld1 is acceptable but space or blanks or any nonnumeric value in fld2 is not a match. even though if fld2 has matching number from fld1.
So for this I want to have a SQL query which once executed should display records like below:-
fld1 fld2
any number of length up to 6 should just display number which matches from fld1 number and in this number there should not be any - nonnumeric value( even it should not have any space or blanks in it)
Thanks
Comment
-
FLD1
Any number ( with max length up to 6)
FLD2
should just display number which matches from FLD1's number and in this number there should not be any - nonnumeric value( even it should not have any space or blanks in it , even though if numeric part matches with FLD1 but it should no have any nonnumeric values in it)
Just putting this comment as in last post #45 acceptable values for fld1, fld2 got misaligned.
Thanks
-
Comment