You can translate the question and the replies:

Using an interpolate variable in FROM clause

Hi team, I am trying to create a base view from a query based on an interpolate variable within the FROM clause. (SELECT * FROM {schema}.@TABLENAME). I want to do this because the platform I am trying to reach (OutSystems) uses a database with 2 layers. A metadata and physical layer. The metadata layer contains the information on which entity is linked to which physical datatable. For example: entity: customer is linked to physical tablename: OSUSR_XYZ_CUSTOMER. Im not aloud to directly query this physical table because they are continuously changing, so a direct query might not work once a tablename has changed. So to summarize I want to create a base view from query where I do the following: SELECT * FROM {schema}.@TABLENAME where TABLENAME = <query to get the real table name>. I tried to do this but I don't think Denodo Express allows me to use a query as a value for an interpolate variable. So I tried the following, which works for 50%: Create 3 views (2 base views and 1 derived view): 1. Create base view from query -> baseview_Tablename1 (SELECT * FROM {schema}.@TABLENAME) where tablename = name of the physical table within the database. This gives an output schema of the current physical table. 2. Create base view form query -> baseview_Tablename2(query to get the physical tablename). This outputs only the physical tablename of a certain entity. 3. Create a derived view on baseview_Tablename 1.@TABLENAME = baseview_Tablename2.physical_table_name. This only works for 50% because of the following reasons: 1. When the tablename changes and the output schema stays the same, this solution will work and the derived view can still be queried without any issues. 2. When the output schema of the 1st base view changes, the derived view doesn't function anymore untill a Source refresh has been executed on baseview_Tablename1. This source refresh however requires a parameter for the Tablename, as the name has changed I don't know the new name so I can't execute a source refresh. As a final remark the following should happen (in my eyes): Base view 1: SELECT * FROM {schema}.@TABLENAME (where the variable can be left open so no static outputschema gets created). Base view 2: query to get the physical table name. Derived view: SELECT * FROM {schema}.@TABLENAME (where @TABLENAME = the answer of base view2). Im sorry for the lenghty post but I wanted to explain this bottleneck has thorough as possible, thanks!

1 Answer

Hi, Adding the interpolate variable in the from clause for creating a base view would work when internal schema of views doesn't change. If it changes, you would need to perform a source refresh. An automatic source refresh is not a best practice and it cannot be done as it will affect its dependent views and web services. I would create a job in Denodo Scheduler to send an email notification when there are any schema change in the data source.Each time a schema change is detected, you could perform the source refresh in Virtual DataPort Administration tool. You could refer the KB article[ How to detect changes](https://community.denodo.com/kb/view/document/How%20to%20detect%20changes%20in%20data%20sources?category=Data+Sources) to know the detailed steps to follow for detecting changes in data sources and automate email notification in scheduler. Hope this helps.
Denodo Team
05-04-2017 08:23:19 -0400
You must sign in to add an answer. If you do not have an account, you can register here