ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

trimming field value

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • #31
    Copy-paste from your JOIN, if that is what you want.

    Comment


    • #32
      that i already mentioned (for post#20) it was not working as per explanation in post #25


      Thanks

      Comment


      • #33
        Show the the latest SELECT with modified fields that you tried.
        You can run the SELECT outside the VIEW, then modify your VIEW when you are satisfied.
        I would think the logic in your join would work in your select.
        Last edited by MFisher; June 24, 2022, 10:02 AM.

        Comment


        • #34
          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


          • #35
            You still have unmodified fields in your select, instead of using the JOIN logic.
            If you ask for only FLD1, it will give you FLD1

            Code:
            SELECT A.FLD1 , B.FLD2

            Comment


            • #36
              Originally posted by MFisher View Post
              You still have unmodified fields in your select, instead of using the JOIN logic.
              If you ask for only FLD1, it will give you FLD1

              Code:
              SELECT A.FLD1 , B.FLD2
              i selected these fields only still result is not as per expectation.


              thanks

              Comment


              • #37
                You have to tell SQL what you want to see. Currently you are asking for FLD1 , FLD2 as they are stored in the file, so that is what it gives you.

                Comment


                • #38
                  i want sql to show me the records matching my expected selection criteria in post number #25 for this what should i tell sql?


                  thanks

                  Comment


                  • #39
                    Use the same logic in your join, in your select.

                    Comment


                    • #40
                      i tried select * from mylib/file3 where fld1 = INT(NULLIF(REGEXP_REPLACE(FLD2, '[^0-9]',''),''))

                      but got same result no change.


                      Thanks

                      Comment


                      • #41
                        SELECT *
                        pulls fields from the file. If you want something different, you have to specifically code it in your SELECT fields list.
                        Copy your JOIN logic into your SELECT
                        Last edited by MFisher; June 24, 2022, 11:13 AM.

                        Comment


                        • #42
                          ok, but apart from this would where clause be the same here like -where fld1 = INT(NULLIF(REGEXP_REPLACE(FLD2, '[^0-9]',''),''))?


                          thanks

                          Comment


                          • John192
                            John192 commented
                            Editing a comment
                            ..also what would be the single SQL statement to match this requirement here?


                            Thanks

                        • #43
                          I don't know what you want in a WHERE clause. Start by selecting all records, then add WHERE based on your requirements.
                          I sometimes use LIMIT if I want a small result set during development.
                          Last edited by MFisher; June 24, 2022, 11:28 AM.

                          Comment


                          • #44
                            Ok so what would be the exact single line SQL statement to get desired result here ?


                            Thanks

                            Comment


                            • #45
                              Originally posted by Vectorspace View Post
                              The 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]',''),''))
                              Basically I don't want field2's character string( which is of length 9) to be replaced with for it's characters whether it's blanks or nonnumeric values what I want is when i compare both fields
                              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


                              • John192
                                John192 commented
                                Editing a 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
                            Working...
                            X