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

    Support in Registry Replication

    cristopher.torres
    cristopher.torres Aug 2, 2017 10:20 AM

      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.

      • 558 Views
      • Tags:
        • 1. Re: Support in Registry Replication
          rareddy
          rareddy Aug 2, 2017 12:08 PM (in response to cristopher.torres)

          See sql - What is the difference between UNION and UNION ALL? - Stack Overflow

            • Actions
          • 2. Re: Support in Registry Replication
            shawkins
            shawkins Aug 2, 2017 12:47 PM (in response to rareddy)

            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

              • Actions
            • 3. Re: Support in Registry Replication
              cristopher.torres
              cristopher.torres Aug 3, 2017 2:32 PM (in response to shawkins)

              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

                • Actions
              • 4. Re: Support in Registry Replication
                rareddy
                rareddy Aug 4, 2017 3:43 AM (in response to cristopher.torres)

                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?

                  • Actions