Remove Duplicate Tables

Why this procedure?

Consider the following SQLs:

    Select  firstname,  lastname  from  employee 
    Select  firstname,  lastname  from  myschema.employee  
  

This basic example creates an ambiguity:

By default, the Visual Expert code parsers cannot know whether employee and myschema.employee refer to the same table. Unless we remove this ambiguity, Visual Expert will duplicate the table employee.

To avoid similar issues in your code, you need to specify a schema, database and server, that Visual Expert will apply by default when they are not explicitly indicated with a prefix before the Oracle or SQL Server object.

In the example above, if myschema is specified as default schema, then the Visual Expert code parser will automatically identify the table employee as myschema.employee, and the table employee will not be duplicated in the VE project.

Identify your schema, database and server name.

First, find the name of the database and schema containing the duplicated objects.
Then, use these values as default database and schema in your VE project.

a. Open a list of objects in Visual Expert and select some duplicated objects.

b. Click "Locate" in the navigation bar : the database and schema containing the objects are displayed.

c. note down the server, database and schema names.

Specify schema/database/server name in VE settings.

• Open your Visual Expert Expert Project
• Open the menu [Project > Manage Source Code]
  
• Select the first item in the table – click on “Default DB Items” – enter the name of the default Server, Database and Schema.
  
• Select the second item in the table (Oracle PL/SQL code or SQL Server T-SQL code) - click on “Default DB Items” – enter the name of the default Server, Database and Schema.
  Important note: it is key to specify these settings for both types of code (PB and Oracle). They rely on 2 separate code parsers, both parsers need to identify properly the DB Objects referenced in the code.
  
• Click OK, close the settings window and launch the code analysis as prompted by VE.
  

Check the results

Once the analysis process is completed, check that the objects are no longer duplicated. 

Eventually, you will see both PowerBuilder and Oracle or SQL Server objects in the treeview: