Introduction

A metadata query is a SQL query (a select query most of the time), which can be reversed and used like a datastore.

This feature was introduced with Stambia S18.0.3 and it allows to have custom datastores representing SQL queries.

 

Creating a metadata query

The first step is to create a query folder, in which our queries will be stored :

Right click on the server node > New > Query Folder

Then, give a name to the folder. My Queries for example.

Now, you can create a query with :

Right click on a query folder > New > Query

Configure the query with a name and a sql query, and finally reverse it with :

Right click > Actions > Reverse

Result :

 

A query defined like that will be always selecting from the table HOTEL_MANAGEMENT.T_CUSTOMER (since the name of the schema is hardcoded).

To make it dependent on the configuration used on the metadata you can use the expression of this type :

SELECT * FROM  {./md:objectPath(ref:schema('METADATA_SCHEMA_NAME'), 'TABLE_NAME')}

For example:

SELECT * FROM {./md:objectPath(ref:schema('HOTEL_MANAGEMENT'), 'T_CUSTOMER')}

This allows to get the schema name from HOTEL_MANAGEMENT metadata element, so in case you apply a configuration for this element and change its 'Schema Name' property this change will be reflected in the executed query.

 

Using a Metadata query

Like said before, a query can be used like any other datastore.

Note :

As metadata queries are most of the time select queries, there is no sense to use it as target