Skip to content

Combine tables across namespaces in a SQL Query – Part 2

The function in Caché Monitor is called “Create mapped table”.

  1. Create a new Namespace with a databaseTo try this feature it is recommended to create a new empty Namespace (and database for this namespace), e.g. called “CM_FEDERATED” as the federated namespace. All tables you want to JOIN in a query must be mapped in this namespace. Mapped means that only the meta data of the tables\classes are copied into the federated namespace, the data remains in their original namespace.
  2. Select the tablesTo do this, open up your “source” namespaces one by one and select the individual tables in Table Browser (select “tables” in server navigator to open), right click on the selection and choose “Create mapped table”. You get asked about the destination namespace, choose the federated namespace and all done!

    create_mapped_table
    You can now connect to the federated namespace e.g. via Query Analyzer and query your tables.

 

Warning! Please be careful if you execute a “DROP TABLE” or just delete operations on a “mapped table”, the original data will be deleted!!

Hint: To remove a table (class) from the federated Namepace, just delete the class! Do not execute a drop table!

 

Read more about Extended Global Reference an how it works: http://docs.intersystems.com/cache52/csp/docbook/DocBook.UI.Page.cls?KEY=GGBL_structure#GGBL_str_extrefs

There are some restrictions in this version:

  • DefaultStorage is required for mapped tables
  • The table name has to be unique in the federated namespace