You can translate the question and the replies:

Connection with an OutSystems database

Hi team! We've succesfully made a connection between a OutSystems database and Denodo. OutSystems creates databases and tablenames automatically and works with a metadata layer. There is a metadata table which contains the metadata and the active tablename at that moment. Example: metadata is called 'Order' and tablename is called 'xyz1' today, but next week (after deploying) the same tablename has changed into 'xyz2'. The tablenames are physical tables with data. We are wondering if it is possible in Denodo to create a base view that automatically refreshes the physical tablename and its data by reffering to the metadata table. Kind regards, Nita
user
20-03-2017 04:30:03 -0400
code

3 Answers

Hi, In your situation, I would try to create the base view “bv1” using the option “Create from query” and specified the table name using an interpolation variable. For example like this: Select * from @{table_name} By doing this, the name of the table in the source needs to be provided at execution time. Then I would create another base view “bv2” accessing to the metadata information on the source. Therefore, the execution of this “bv2” will return the name of the table, for example “xyz1” based on your example. After this I would create a new derived view “bv3” by joining the above two views “bv1” and “bv2”. The join condition should use the resultant field for “bv2” view and the “table_name” field for “bv1” view. With all of this you will only need to query the “bv3” view that will always use the correct table name in your source. Hope this helps!
Denodo Team
24-03-2017 00:37:58 -0400
code
Hi Team, Thanks for the response. However, I have already tried this option and it doesn't work, because I have to enter the value for the interpolation variable. If I set this variable into tabelname 'xyz1', the third view will not change if the tablename has changed into 'xyz2'.. The value for the variable tablename creates field in the base view, but 'xyz1' has different fields then 'xyz2'. After a new deployment we do not want to use xyz1 but xyz2. I have tried working with the SQL fragment as option by entering a value for the interpolation variable, but it seems that subqueries are not allowed. Kind regards, Nita
user
24-03-2017 04:17:31 -0400
Hi, In my case I would not enter the value for the interpolation variable, as I have removed the field “TABLENAME” in the derived view ‘bv3’. I was able to change the table name only when the schema of both tables ‘xyz1’and ‘xyz2’ are the same. If the schema is different, I would use the option “Source Refresh” available in the Edit tab, in order to apply the new schema for the view in Virtual DataPort. Hope this helps!
Denodo Team
29-03-2017 08:56:41 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here