GROUP BY PROPER_COLUMN HAVING COUNT ( * ) > 1 ) ( SELECT PROPER_COLUMN AS FOR_ERROR, COUNT (* ) FROM T1 ( SELECT DISTINCT PROPER_COLUMN, LONG_COLUMN FROM COLTRANS ) , ORDER BY LIBRARY, TABLE_NAME, CURRENT_COLUMN, COLUMN_SEQUENCE SELECT 'Column Name' AS PROBLEM_TYPE, LIBRARY, TABLE_NAME ,ĬURRENT_COLUMN, COLUMN_SEQUENCE, PROPER_COLUMN , LONG_TABLE_NAME LIKE '%+_+_%' ESCAPE '+' LONG_TABLE_NAME LIKE '%+_+_%' ESCAPE '+' OR SELECT 'Table Name' AS PROBLEM_TYPE, LIBRARY, LONG_TABLE_NAME If you come up with any extra result sets or tweaks, please let me know!
DATABASE PART 1 CODE
The next piece of code shows the DDL to create the ANALYZE_NAMES stored procedure. We can now call ANALYZE_NAMES again and keep going until all potential problems have been corrected. This is caused by different entities being given the same description. The fourth result set highlights any potential problems where generated long column names have different proper names (a field name without a prefix or a suffix), as shown below. If anything, this was a mistake in the original DDS, so, there is nothing we need to do to correct the problem. In this instance, although two items are listed, it is not a problem. The third result set highlights any potential problems where a proper name (a field name without a prefix or a suffix) has different generated long column names, as shown in the next figure. Where (library, table_name, current_column) = We can correct these names using the SQL statements: This is because the field did not have a text description, so one could not be generated. The name for the PMSTOH field is *MISSING.This occurred because there was a single apostrophe (before the S) that got replaced by an underscore. The contacts first and last name have ‘_S_’ in the middle of the name.In this instance, there are two problems: The second result set highlights any problems with the generated long column names, as shown below. Where long_table_name = 'SALES_HISTORY_FILE' Set long_table_name = 'SALES_HISTORY_FILE' We can correct the long table name using the SQL statement: The problem with the name is that there is a double underscore between SALES and HISTORY. The first result set highlights any problems with the generated long name for the physical file, as shown the figure below.
![database part 1 database part 1](https://cdn-ak.f.st-hatena.com/images/fotolife/p/pokutuna/20180626/20180626052804.png)
It returns four result sets and is called as follows: The stored procedure does not have any parameters.
![database part 1 database part 1](https://image.slidesharecdn.com/databasemarketingpart1-roadmaptosuccess-141211132452-conversion-gate01/95/database-marketing-part-1-roadmap-to-success-1-638.jpg)
We can use the stored procedure ANALYZE_NAMES to help us check for some of the more common issues with name generation. Since the new column names were generated from the text descriptions of the original fields, there is a good chance that some of the generated names might not be quite right.
![database part 1 database part 1](https://miro.medium.com/max/623/1*4zcKyBHJQYZ2FrtsTAtBeA.png)
In my first article, we had reached the stage where we had extracted table and column definitions into our two conversion tables (TABLE_TRANSLATION and COLUMN_TRANSLATION), as shown below:
DATABASE PART 1 SERIES
In this article, the second in a series of three, we continue the refacing process by analyzing and correcting our new naming. So we have to spend some time ensuring that our names are right. The whole purpose of refacing our database is to give proper names to tables and columns.