ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Joining of two files

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

  • Joining of two files

    Hi,


    I have two files lets say file1 and file2.

    file1 has multiple fields but let's say currently we are just focused for it's field1 and similarly file2 have multiple such fields and we are just concerned with field2.

    field1 of file1 is of packed decimal with 6 length.

    field2 of file2 is of character data type and of length 9.

    Now we want to join file1's field1 to the fiel2's field2 in such a way that we are sure that field2 (would have that matching value from file1's field1 in it's( inside field2) first 6 positions only.
    and even if it's found in field2 beyond 6th position then we are not concerned with that match.

    So how to join these files to get the matching records from fiile1's field1 to file2's field2.

    I mean what should be the join SQL query to get these matching records?



    Thanks







  • #2
    Isn't this the same question you just asked recently in this thread ?
    Hi, How to trim a field value in SQL query in such a way so that we could just remove extra two characters coming in this field at the end. Thanks


    REGEXP_REPLACE was suggested as a solution.

    Comment


    • #3
      No, that was if the field2 can have that matching number from field1 ( at any position in field2- that was a different question).

      and this question is specific if field1 is present at first 6 positions of field2 ( there could be some nonnumericvalues as wellin these 6 positions)

      but we have to ignore such records need to focus only matching records from fleld1 to field2 ( need to check just first 6 positions correponding to field1 value)

      so What should be the join sql statement for this condition here?


      thanks



      Comment


      • #4
        In the other thread, I suggested this, which was ignored.
        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 .
        If you supplied clear examples of the schemas & data & expected results, you might find others more willing to help. But vaguely worded posts, that leave a lot of guessing and assumptions, turns into pages of back & forth posts and lots of wasted time.
        Last edited by MFisher; June 29, 2022, 12:06 PM.

        Comment


        • #5
          in that thread I already provided the same but did not get solution for my query.


          Thanks

          Comment


          • #6
            Originally posted by John192 View Post
            in that thread I already provided the same but did not get solution for my query.


            Thanks
            also this is different query from that thread.


            thanks

            Comment


            • #7
              Combine SUBSTR with REGEXP_REPLACE

              Comment


              • #8
                Originally posted by MFisher View Post
                Combine SUBSTR with REGEXP_REPLACE
                can you please provide exact SQL query for the same.


                Thanks

                Comment


                • #9
                  Originally posted by John192 View Post

                  can you please provide exact SQL query for the same.

                  Thanks
                  No, I do not know the exact query. What have you tried so far, and what is your result ?

                  If there were CREATE TABLE statements for the files, and INSERT statements to populate the files with sample data, then others would have a common starting point.

                  Comment


                  • #10
                    I already provided description length data types of these fields , (create table statement not sure about that..)

                    I tried below:-

                    Code:
                    select A.fld1,B.fld2 from lib1/file1 a left join lib2/file2 b on A.FLD1 = INT(REGEXP_REPLACE(SUBSTR(B.FLD2,1,6), '[^0-9]' , ''))
                    then getting the error 'selection error involving field *N' when i did wrkjob -f10-shift+f6 then got this message ' select or omit on field cast(translate(cast(cast(REGEXP_REPLACE(CAST(MEMBE R FILE1.

                    CAUSE--A select or omit error occurred in record 1, record format * first ,member number 1 of file file1 in library1 ,because of condition 6 of the following
                    conditions:
                    6- A data mapping error occurred on the field before the select or omit operation was attempted.




                    Thanks

                    Comment


                    • #11
                      Originally posted by John192 View Post
                      I already provided description length data types of these fields , (create table statement not sure about that..)

                      I tried below:-

                      Code:
                      select A.fld1,B.fld2 from lib1/file1 a left join lib2/file2 b on A.FLD1 = INT(REGEXP_REPLACE(SUBSTR(B.FLD2,1,6), '[^0-9]' , ''))
                      then getting the error 'selection error involving field *N' when i did wrkjob -f10-shift+f6 then got this message ' select or omit on field cast(translate(cast(cast(REGEXP_REPLACE(CAST(MEMBE R FILE1.

                      CAUSE--A select or omit error occurred in record 1, record format * first ,member number 1 of file file1 in library1 ,because of condition 6 of the following
                      conditions:
                      6- A data mapping error occurred on the field before the select or omit operation was attempted.




                      Thanks
                      just to explain please find below sample records for these fields:-

                      Code:
                      field1                                       fild2
                      1234                                         *
                      1234                                         *
                      1234                                         *12345
                      12345                                       012345
                      
                      
                      
                      so for this  i am expecting a ideal join sql statement should provide me the matching records from both of above fields  like below:-
                      
                      
                      field1                                         field2
                      12345                                         012345


                      Thanks



                      Comment


                      • #12
                        It doesn't like FIELD2 that contains only '*'

                        Comment


                        • #13
                          Originally posted by MFisher View Post
                          It doesn't like FIELD2 that contains only '*'
                          so if the JOIN SQL statement does not like those records from file2's field2 who just have values like these stars(*) then how can i make it to like in such a way so that i get good join records like i mentioned in my example in post #11?

                          i mean what correction is required or what should be the correct SQL join statement here to get desired matching records?


                          Thanks.

                          Comment


                          • #14
                            left join QTEMP.FILE_2 b on A.FIELD1 = CASE WHEN (REGEXP_REPLACE(SUBSTR(B.FIELD2,1,6), '[^0-9]' , '')) > '0' then INT(REGEXP_REPLACE(SUBSTR(B.FIELD2,1,6), '[^0-9]' , ''))
                            else 0 end

                            Comment


                            • #15
                              Try

                              Code:
                              on A.FLD1 = int(nullif(regexp_substr(B.FLD2,'^[0-9]*'),''))
                              regexp_substr() extracts the string that matches a regex pattern
                              The pattern is ^[0-9]*
                              Which is start of string, then zero or more digits (0-9).
                              It will return all the leading numeric characters. If the first character is not a digit it will return an empty string instead

                              nullif(...,'') converts an empty string to null. For safety as int() cannot handle blank but can handle null. This takes care of the '*' records

                              int(...) converts to numeric so it can be directly compared to A.FLD1

                              Comment

                              Working...
                              X