Support in Registry Replication

[cristopher.torres]

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.

[rareddy]

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

[shawkins]

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

[cristopher.torres]

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

    

    

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

       

         

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

         

          

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

              

               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:

               

[rareddy]

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?