4 Replies Latest reply on Aug 4, 2017 3:43 AM by Ramesh Reddy

    Support in Registry Replication

    Cristopher Torres Novice

      Hello,

      We have a table of clients in three different databases which we have integrated and created a single client table using a Select Union, as a result we have something like this:

       

      ID                     Name                  Origin

       

      72920465        Cristopher T.      Campo Fe

      78968675        Richard G.          Presta Fe

      72920465        Cristopher T.       Fe Salud

       

      As we can see in the example we have obtained a duplicate client because it has two different source fields, which we hope is to obtain a single record of the client with the concatenated source fields.

      ID                     Name                  Origin

       

      72920465        Cristopher T.      Campo Fe, Fe Salud

      78968675        Richard G.          Presta Fe

       

      We hope they can help us with this solution,

      Thank you very much.

        • 2. Re: Support in Registry Replication
          Steven Hawkins Master

          It will take more than a union unfortunately.  You would need something like:

           

          select max(ID), name, string_agg(origin, ',') from (select ... union ...) group by name

          • 3. Re: Support in Registry Replication
            Cristopher Torres Novice

            Thanks Ramesh and Steven for helping me with this solution.

            Steven told you that I applied the sentence you suggested and I was even investigating it. However, the teiid does not recognize the String_agg function.

            I was thinking that maybe this is because of the way I designed the preview before executing the sentence you recommended.

            Here are the steps of my development:

             

            1. First I create a source view for each client table

                 1.1.png

                 1.2.png

                 2. Then I created a stored procedure so that each view indicated the source where it came from.

                    2.1.png

                      2.2.png

                      3.Afterwards, I placed this stored procedure in a view.

                      3.1.png

                        

                       3.2.png

             

                           4.Once I got a view of each client stating their source, I applied a select union to have a single client view.

                           4.2.png

             

                           5.But as the discussion began in the blog, clients that have two source sources are duplicated in the view, for this I applied the function                            String_agg and as a result I got this:

             

                            5.1.png

            • 4. Re: Support in Registry Replication
              Ramesh Reddy Master

              You do not really need the procedure layer and then back to view layer, you could have done in the first view layer. For example:

               

              CF_clientView: select 'CompoFe' as flunte, a.dni as dni, ... FROM clientCF as a;

               

              FS_clientView: select 'FeSalaud' as flunte, a.dni as dni, ... FROM clientFS as a;

               

              Then you are at the last step before the union view. The string_agg function looks right. What the JDV version you are running?