Problem Summary
How to add multiple instances of a table to a channel?
Answer
In certain situations, e.g. when merging or splitting rows across tables, it is necessary to add multiple instances of the same base table in a channel.
The usual procedure, using Table Select, does not support adding a table multiple times. Without Table Select adding a table would mean manually adding all columns as well, which is cumbersome especially when the table has lots of columns. An easy way to do this is:
1. Right-mouse click on Tables and choose “New Table”; For Base Table Name give the name of the table in the database, e.g. mytable and for the Table Name (i.e. the ‘alias’ for this table inside Local Data Processing)
2. Pick a different name for each instance (e.g using a sequence number: mytable1, mytable2). Repeat this until you have the number of instances you require.
3. Go into the Table Select and connect to a location. You will see the newly created tables marked as “Different Columns”.
4. Select all of them and click replace. Local Data Processing now replaces the empty column list with the list retrieved from the database ‘describe’ of the table.
Alternatively, you could use the Local Data Processing XML import/export functionality to achieve this:
1. Add one instance of the table using Table Select in the usual way.
2. Export the Local Data Processing catalogs into an XML file either from the GUI (right-mouse click on the channel) or using command hvrcatalogexport.
3. Open the XML file in a text editor and search for the definition of the table:
<hvr_table chn_name="mychannel" tbl_name="mytable" tbl_base_name="mytable"/>
4. And copy this definition (changing the value for tbl_name) until you have the required number of instances:<hvr_table chn_name="mychannel" tbl_name="mytable2" tbl_base_name="mytable"/> <hvr_table chn_name="mychannel" tbl_name="mytable3" tbl_base_name="mytable"/>
5. Search for Local Data Processing's column definitions for this table:
<hvr_column chn_name="mychannel" tbl_name="mytable" col_sequence="1" col_name="prod_id" col_key="1" col_datatype="integer4"/> <hvr_column chn_name="mychannel" tbl_name="mytable" col_sequence="2" col_name="prod_price" col_datatype="decimal" col_length="10,2"/>
6. Copy all of the column information for this table, each time replacing the tbl_name with the correct value until all of the added table instances have column information
7. Import the resulting XML catalog file either from the GUI (right-mouse click on hub) or using command hvrcatalogimport