set search_path to castoncast_ora_local;

create or replace view ORAQRs
as
select distinct 
	case when c.METRIC_NAME = 'Use varchar2 instead of char and varchar' 
					then 'VARCHAR2 and NVARCHAR2 should be used'
	     when c.METRIC_NAME in ( 'Avoid SQL queries that no index can support' ,
				'Avoid SQL queries using functions on indexed columns in the WHERE clause',
				'Avoid SQL queries not using the first column of a composite index in the WHERE clause') 
					then 'Avoid non-indexed SQL queries'
		when c.METRIC_NAME in ( 'Avoid SQL queries on XXL tables that no index can support' ,
				'Avoid SQL queries on XXL Tables using Functions on indexed Columns in the WHERE clause',
				'Avoid SQL queries on XXL tables not using the first column of a composite index in the WHERE clause') 
					then 'Avoid non-indexed XXL SQL queries'
	    when c.METRIC_NAME = 'Avoid definition of synonym as PUBLIC in PL/SQL context' 
					then 'Prefer PRIVATE to PUBLIC synonym'
	    when c.METRIC_NAME = 'Avoid Artifacts with Group By' 
					then 'Avoid using the GROUP BY clause'
	    when c.METRIC_NAME = 'Avoid orphaned synonyms in PL/SQL context' 
				then 'Avoid orphaned synonyms'
	    when c.METRIC_NAME = 'Avoid queries using old style join convention  instead of ANSI-Standard joins' 
				then 'Avoid queries using old style join convention instead of ANSI-Standard joins'
	    when c.METRIC_NAME = 'Avoid Artifacts with queries on more than 4 Tables' 
				then 'Avoid Artifacts with queries on too many Tables and/or Views'
	    when c.METRIC_NAME = 'Do not mix Ansi joins syntax  with Oracle proprietary joins syntax in the same query' 
				then 'Do not mix ANSI and non-ANSI JOIN syntax in the same query'
	    when c.METRIC_NAME = 'Avoid synonym with both private & PUBLIC Definition in PL/SQL context' 
				then 'Avoid synonym with both private and public definition'
	    when c.METRIC_NAME = 'Avoid looping chain of synonyms in PL/SQL context' 
				then 'Avoid looping chain of synonyms'
	    when c.METRIC_NAME = 'Avoid improperly written triangular joins with XXL tables in PL/SQL code' 
				then 'Avoid improperly written triangular joins with XXL tables'
	    when c.METRIC_NAME = 'Avoid exists independent clauses' 
				then 'Avoid exists and not exists independent clauses (SQL)'
	    when c.METRIC_NAME = 'Avoid using execute immediate' 
				then 'Avoid using dynamic SQL in SQL Artifacts'
	    when c.METRIC_NAME = 'Avoid using LONG & LONG RAW datatype for Table Columns' 
				then 'LONG and LONG RAW datatypes should no longer be used'
	else c.METRIC_NAME 
	end as QualityRuleName, 
case
	when n.OBJECT_TYPE_STR = 'Oracle check table constraint' then 'Check Constraint'
	when n.OBJECT_TYPE_STR = 'Oracle default table constraint' then 'Default Constraint'
	when n.OBJECT_TYPE_STR = 'Oracle foreign key table constraint' then 'Foreign Key'
	when n.OBJECT_TYPE_STR = 'Oracle primary key table constraint' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle bitmap index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle function based index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle table' then 'Table'
	when n.OBJECT_TYPE_STR = 'Oracle instance' then 'Instance'
	when n.OBJECT_TYPE_STR like '%collection%' then 'Collection'
	when n.OBJECT_TYPE_STR like '%object%' then 'Type'
	when n.OBJECT_TYPE_STR = 'Oracle function' then 'Function'
	when n.OBJECT_TYPE_STR = 'Oracle private stored function' then 'Function'
	when n.OBJECT_TYPE_STR like '%procedure%' then 'Procedure'
	when n.OBJECT_TYPE_STR like '%trigger%' then 'Trigger'
	when n.OBJECT_TYPE_STR like '%package header%' or n.OBJECT_TYPE_STR like '%package body%' then 'Package'
	when n.OBJECT_TYPE_STR like '%column%' then 'Column'
	when n.OBJECT_TYPE_STR like '%synonym%' then 'Synonym'
	when n.OBJECT_TYPE_STR like '%schema%' then 'Schema'
	when n.OBJECT_TYPE_STR like '%unique table constraint%' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR like '%view%' then 'View'
	when n.OBJECT_TYPE_STR like '%sequence%' then 'Sequence'
	when n.OBJECT_TYPE_STR like '%Database Link%' then 'Database Link'
	when n.OBJECT_TYPE_STR like '%package cursor%' then 'Package Cursor'
	when n.OBJECT_TYPE_STR like '%cursor datatype%' then 'Cursor Datatype'
	when n.OBJECT_TYPE_STR like '%Package Variable%' then 'Package Variable'
	else n.OBJECT_TYPE_STR 
end ObjectType, 
 lower(substring(n.OBJECT_FULLNAME, position('.' in n.OBJECT_FULLNAME) + 1)) as ObjectName
from DSS_METRIC_RESULTS r
join DSS_METRIC_TYPES c on r.METRIC_ID = c.METRIC_ID+1
join CDT_OBJECTS n on n.OBJECT_ID = r.OBJECT_ID 
where n.OBJECT_TYPE_STR != 'Oracle Database Subset'
order by QualityRuleName, ObjectType, ObjectName;

create or replace view NumberOf_ORAQRs
as
select distinct  
	case when c.METRIC_NAME = 'Use varchar2 instead of char and varchar' 
					then 'VARCHAR2 and NVARCHAR2 should be used'
	     when c.METRIC_NAME in ( 'Avoid SQL queries that no index can support' ,
				'Avoid SQL queries using functions on indexed columns in the WHERE clause',
				'Avoid SQL queries not using the first column of a composite index in the WHERE clause') 
					then 'Avoid non-indexed SQL queries'
		when c.METRIC_NAME in ( 'Avoid SQL queries on XXL tables that no index can support' ,
				'Avoid SQL queries on XXL Tables using Functions on indexed Columns in the WHERE clause',
				'Avoid SQL queries on XXL tables not using the first column of a composite index in the WHERE clause') 
					then 'Avoid non-indexed XXL SQL queries'
	    when c.METRIC_NAME = 'Avoid definition of synonym as PUBLIC in PL/SQL context' 
					then 'Prefer PRIVATE to PUBLIC synonym'
	    when c.METRIC_NAME = 'Avoid Artifacts with Group By' 
					then 'Avoid using the GROUP BY clause'
	    when c.METRIC_NAME = 'Avoid orphaned synonyms in PL/SQL context' 
				then 'Avoid orphaned synonyms'
	    when c.METRIC_NAME = 'Avoid queries using old style join convention  instead of ANSI-Standard joins' 
				then 'Avoid queries using old style join convention instead of ANSI-Standard joins'
	    when c.METRIC_NAME = 'Avoid Artifacts with queries on more than 4 Tables' 
				then 'Avoid Artifacts with queries on too many Tables and/or Views'
	    when c.METRIC_NAME = 'Do not mix Ansi joins syntax  with Oracle proprietary joins syntax in the same query' 
				then 'Do not mix ANSI and non-ANSI JOIN syntax in the same query'
	    when c.METRIC_NAME = 'Avoid synonym with both private & PUBLIC Definition in PL/SQL context' 
				then 'Avoid synonym with both private and public definition'
	    when c.METRIC_NAME = 'Avoid looping chain of synonyms in PL/SQL context' 
				then 'Avoid looping chain of synonyms'
	    when c.METRIC_NAME = 'Avoid improperly written triangular joins with XXL tables in PL/SQL code' 
				then 'Avoid improperly written triangular joins with XXL tables'
	    when c.METRIC_NAME = 'Avoid exists independent clauses' 
				then 'Avoid exists and not exists independent clauses (SQL)'
	    when c.METRIC_NAME = 'Avoid using execute immediate' 
				then 'Avoid using dynamic SQL in SQL Artifacts'
	    when c.METRIC_NAME = 'Avoid using LONG & LONG RAW datatype for Table Columns' 
				then 'LONG and LONG RAW datatypes should no longer be used'
	else c.METRIC_NAME 
	end as QualityRuleName, 
case
	when n.OBJECT_TYPE_STR = 'Oracle check table constraint' then 'Check Constraint'
	when n.OBJECT_TYPE_STR = 'Oracle default table constraint' then 'Default Constraint'
	when n.OBJECT_TYPE_STR = 'Oracle foreign key table constraint' then 'Foreign Key'
	when n.OBJECT_TYPE_STR = 'Oracle primary key table constraint' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle bitmap index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle function based index' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR = 'Oracle table' then 'Table'
	when n.OBJECT_TYPE_STR = 'Oracle instance' then 'Instance'
	when n.OBJECT_TYPE_STR like '%collection%' then 'Collection'
	when n.OBJECT_TYPE_STR like '%object%' then 'Type'
	when n.OBJECT_TYPE_STR = 'Oracle function' then 'Function'
	when n.OBJECT_TYPE_STR = 'Oracle private stored function' then 'Function'
	when n.OBJECT_TYPE_STR like '%procedure%' then 'Procedure'
	when n.OBJECT_TYPE_STR like '%trigger%' then 'Trigger'
	when n.OBJECT_TYPE_STR like '%package header%' or n.OBJECT_TYPE_STR like '%package body%' then 'Package'
	when n.OBJECT_TYPE_STR like '%column%' then 'Column'
	when n.OBJECT_TYPE_STR like '%synonym%' then 'Synonym'
	when n.OBJECT_TYPE_STR like '%schema%' then 'Schema'
	when n.OBJECT_TYPE_STR like '%unique table constraint%' then 'Indexes, Primary Keys and Unique Constraints'
	when n.OBJECT_TYPE_STR like '%view%' then 'View'
	when n.OBJECT_TYPE_STR like '%sequence%' then 'Sequence'
	when n.OBJECT_TYPE_STR like '%Database Link%' then 'Database Link'
	when n.OBJECT_TYPE_STR like '%package cursor%' then 'Package Cursor'
	when n.OBJECT_TYPE_STR like '%cursor datatype%' then 'Cursor Datatype'
	when n.OBJECT_TYPE_STR like '%Package Variable%' then 'Package Variable'
	else n.OBJECT_TYPE_STR 
end ObjectType,
count(1) NumberOf
from DSS_METRIC_RESULTS r
join DSS_METRIC_TYPES c on r.METRIC_ID = c.METRIC_ID+1
join CDT_OBJECTS n on n.OBJECT_ID = r.OBJECT_ID 
where n.OBJECT_TYPE_STR != 'Oracle Database Subset'
group by QualityRuleName, ObjectType
order by QualityRuleName, ObjectType;

set search_path to castoncast_sql_src_local;

create or replace view SQLQRs
as
select distinct 
	case 
		when c.METRIC_NAME like '% (SQL)' then replace(c.METRIC_NAME, ' (SQL)', '')
		else c.METRIC_NAME end as QualityRuleName, 
case
	when n.OBJECT_TYPE_STR like '%Column%' then 'Column'
	when n.OBJECT_TYPE_STR like '%Unique%' or n.OBJECT_TYPE_STR like '%Index%' or n.OBJECT_TYPE_STR like '%Primary%' then 'Indexes Primary Keys and Unique Constraints'
	else n.OBJECT_TYPE_STR 
end ObjectType, 
	lower(n.OBJECT_FULLNAME) as ObjectName
from DSS_METRIC_RESULTS r
join DSS_METRIC_TYPES c on r.METRIC_ID = c.METRIC_ID+1
join CDT_OBJECTS n on n.OBJECT_ID = r.OBJECT_ID 
where n.OBJECT_TYPE_STR != 'SQL Analyzer Subset'
order by QualityRuleName, ObjectType, ObjectName;

create or replace view NumberOf_SQLQRs
as
select distinct  
	case 
		when c.METRIC_NAME like '% (SQL)' then replace(c.METRIC_NAME, ' (SQL)', '')
		else c.METRIC_NAME end as QualityRuleName, 
case
	when n.OBJECT_TYPE_STR like '%Column%' then 'Column'
	when n.OBJECT_TYPE_STR like '%Unique%' or n.OBJECT_TYPE_STR like '%Index%' or n.OBJECT_TYPE_STR like '%Primary%' then 'Indexes Primary Keys and Unique Constraints'
	else n.OBJECT_TYPE_STR 
end ObjectType,
count(1) NumberOf
from DSS_METRIC_RESULTS r
join DSS_METRIC_TYPES c on r.METRIC_ID = c.METRIC_ID+1
join CDT_OBJECTS n on n.OBJECT_ID = r.OBJECT_ID 
where n.OBJECT_TYPE_STR != 'SQL Analyzer Subset'
group by QualityRuleName, ObjectType
order by QualityRuleName, ObjectType;


set search_path to castoncast_sql_src_local;

create or replace view Compare_NumberOfSQLORAQRs
as
select case 
	when sql.QualityRuleName is null then ora.QualityRuleName  
	when ora.QualityRuleName is null then sql.QualityRuleName
	else sql.QualityRuleName  
	end QualityRuleName,
case 
	when sql.ObjectType is null then ora.ObjectType  
	when ora.ObjectType is null then sql.ObjectType
	else sql.ObjectType
	end ObjectType,
case 
	when sql.NumberOf is null then 
		case when ora.QualityRuleName in ('Avoid using "nullable" Columns except in the last position in a Table',
						'Cursor naming convention - prefix control',
						'Avoid Rule HINT /*+ rule */ or --+ rule in PL/SQL code',
						'Avoid SQL queries with implicit conversions in the WHERE clause',
						'Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause') 
						then 'Exists only on PL/SQL Analyzer'
			when ora.QualityRuleName = 'Avoid Artifacts with a Complex SELECT Clause' then 'The rationale describe a client server context, there is no value to apply-it on SQL code'
		else 'Detected only by PL/SQL Analyzer' 
		end
	when ora.NumberOf is null then
		case when sql.QualityRuleName in ('Always define column names when inserting values', 
						'Avoid empty catch blocks', 
						'Avoid non-SARGable queries', 
						'Column references should be qualified',
						'DISTINCT should not be used in SQL SELECT statements', 
						'Never use WHEN OTHER THEN NULL', 
						'Tables aliases should not end with a numeric suffix',
						'SQL "OR" clauses testing equality on the same identifier should be replaced by an "IN" clause',
						'LIKE operator should not start with a wildcard character',
						'Avoid explicit comparison with NULL',
						'Tables should be aliased',
						'Never use WHEN OTHER THEN NULL',
						'View naming convention - character set control (SQL)',
						'Table naming convention - character set control (SQL)',
						'Use ANSI standard operators in SQL WHERE clauses - applying only on the client code',
						'Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries',
						'Avoid using quoted identifiers',
						'Specify column names instead of column numbers in ORDER BY clauses',
						'Avoid NATURAL JOIN queries'
						)
		then 'Exists only on SQL Analyzer'
		else 'Detected only by SQL Analyzer'
		end 
	when sql.NumberOf = ora.NumberOf then '='  
	else (sql.NumberOf - ora.NumberOf)::text
	end "Compared with Oracle" 
from castoncast_sql_src_local.NumberOf_SQLQRs sql full outer join castoncast_ora_local.NumberOf_ORAQRs ora
on ora.ObjectType = sql.ObjectType and ora.QualityRuleName = sql.QualityRuleName
order by QualityRuleName, ObjectType;

-- select * from Compare_NumberOfSQLORAQRs;

create or replace view Compare_DetailsSQLORAQRs
as
select case 
	when sql.QualityRuleName is null then ora.QualityRuleName  
	when ora.QualityRuleName is null then sql.QualityRuleName
	else sql.QualityRuleName  
	end QualityRuleName,
case 
	when sql.ObjectType is null then ora.ObjectType  
	when ora.ObjectType is null then sql.ObjectType
	else sql.ObjectType
	end ObjectType,
case 
	when sql.ObjectName is null then ora.ObjectName  
	when ora.ObjectName is null then sql.ObjectName
	else sql.ObjectName
	end ObjectName,
case 
	when sql.ObjectName is null then 
		case when ora.QualityRuleName in ('Avoid using "nullable" Columns except in the last position in a Table',
						'Cursor naming convention - prefix control',
						'Avoid Rule HINT /*+ rule */ or --+ rule in PL/SQL code',
						'Avoid SQL queries with implicit conversions in the WHERE clause',
						'Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause')
		then 'Exists only on PL/SQL Analyzer'
		else 'Detected only by PL/SQL Analyzer' 
		end
	when ora.ObjectName is null then 
		case when sql.QualityRuleName in ('Always define column names when inserting values', 
						'Avoid empty catch blocks', 
						'Avoid non-SARGable queries', 
						'Column references should be qualified',
						'DISTINCT should not be used in SQL SELECT statements', 
						'Never use WHEN OTHER THEN NULL', 
						'Tables aliases should not end with a numeric suffix',
						'SQL "OR" clauses testing equality on the same identifier should be replaced by an "IN" clause',
						'LIKE operator should not start with a wildcard character',
						'Avoid explicit comparison with NULL',
						'Tables should be aliased',
						'Never use WHEN OTHER THEN NULL',
						'View naming convention - character set control (SQL)',
						'Table naming convention - character set control (SQL)',
						'Use ANSI standard operators in SQL WHERE clauses - applying only on the client code',
						'Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries',
						'Avoid using quoted identifiers',
						'Specify column names instead of column numbers in ORDER BY clauses',
						'Avoid NATURAL JOIN queries'
						)
		then 'Exists only on SQL Analyzer'
		else 'Detected only by SQL Analyzer'
		end 
	else '='  
	end "Compared with Oracle" 
from 
(select QualityRuleName, ObjectType, ObjectName
from castoncast_sql_src_local.SQLQRs 
where QualityRuleName not in ('Always define column names when inserting values', 
						'Avoid empty catch blocks', 
						'Avoid non-SARGable queries', 
						'Column references should be qualified',
						'DISTINCT should not be used in SQL SELECT statements', 
						'Never use WHEN OTHER THEN NULL', 
						'Tables aliases should not end with a numeric suffix',
						'SQL "OR" clauses testing equality on the same identifier should be replaced by an "IN" clause',
						'LIKE operator should not start with a wildcard character',
						'Avoid explicit comparison with NULL',
						'Tables should be aliased',
						'Never use WHEN OTHER THEN NULL',
						'View naming convention - character set control (SQL)',
						'Table naming convention - character set control (SQL)',
						'Use ANSI standard operators in SQL WHERE clauses - applying only on the client code',
						'Use MINUS or EXCEPT operator instead of NOT EXISTS and NOT IN subqueries',
						'Avoid using quoted identifiers',
						'Specify column names instead of column numbers in ORDER BY clauses',
						'Avoid NATURAL JOIN queries'
						)
) sql full outer join
 (select QualityRuleName, ObjectType, ObjectName
from castoncast_ora_local.ORAQRs 
where QualityRuleName not in ('Avoid using "nullable" Columns except in the last position in a Table',
						'Cursor naming convention - prefix control',
						'Avoid Rule HINT /*+ rule */ or --+ rule in PL/SQL code',
						'Avoid SQL queries with implicit conversions in the WHERE clause',
						'Avoid SQL queries on XXL Tables with implicit conversions in the WHERE clause',
						'Avoid Artifacts with a Complex SELECT Clause' )
) ora
on ora.ObjectType = sql.ObjectType and ora.QualityRuleName = sql.QualityRuleName
	and ora.ObjectName = sql.ObjectName
where ora.ObjectName is NULL or sql.ObjectName is NULL
order by QualityRuleName, ObjectType, ObjectName;

-- select * from Compare_DetailsSQLORAQRs;
