You can translate the question and the replies:

Denodo testing tool, compare views with different names

Hi, With Denodo Testing Tool, I would like to compare number of lines of my source tables in db2 vs views in Denodo, problem is that names are differents. ``` %CONTEXT tablenames[] = a, b, c interfacenames[] = aa, bb, cc schemavdp = eicorea schemadb2 = a prefixe = i_ %EXECUTION[query] { ds:vdp } select count(1) as RESULT from ${schemavdp}.${prefixe}${interfacenames} %RESULTS[query] { ds:db2 } select count(1) as RESULT from ${schemadb2}.${tablenames} ``` I've got 7000 tables to compare, if I put each names in CONTEXT directive it wiil perform a test for each combinaison, and most will be failed. For example I would like to compare table a to interface aa only. I've got a correspondence table to retrieve interfaces names with this query : ``` select interface from dscore_a.bv_correspondance_table where nom_table_srce in ${tablenames}) ``` I tried something like that : ``` %EXECUTION[query] { ds:vdp } select count(1) as RESULT from concat('eicorea.i_', (select interface from dscore_a.bv_correspondance_table where nom_table_srce = 'a')) ``` but it returned 1 instead of the real number of rows of interface aa. I can't find any solution to perform this test.. Thanks
user
01-09-2020 10:39:57 -0400

1 Answer

Hi, In order to achieve your required scenario, I would do the following steps: * First I would utilize the Denodo Predefined stored procedures such as [GET_VIEWS](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_views#get-views) to get the list of views of the database, [VIEW_DEPENDENCIES](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/view_dependencies#view-dependencies) to get the respective view name and its type, and [GET_SOURCE_COLUMNS](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_source_columns) to get the dependent base view name along with its source table. Then, create the respective join views on top of them to get the interface view name and its Dependent base view name along with its source table name. * Then, I would create a JDBC data source for Denodo by choosing the database adapter as 'Denodo Virtual DataPort 7.0' in the Virtual DataPort and create a view for getting count with an interpolation variable like the example mentioned below: **Select count(1) from @input** * After this, I would join the view which provides information about the list of interface views with the above-created count view to get number of records present in each interface view. Similarly, I would do the same to get the total number of records present in each table in the underlying source. * Finally, I would compare both the views outcomes in the Denodo test scripts which will compare my source tables records with each interface views records. If you need more clarifications and have a valid support account, I would recommend that you create a support case in the [Denodo Support Site](http://support.denodo.com/). Hope this helps!
Denodo Team
03-09-2020 08:56:29 -0400
You must sign in to add an answer. If you do not have an account, you can register here