REPLACE MACRO BEDW_${TDENV}_PERF_M.MODEL_STANDARDS_CLOB_USAGE

/*-------------------------------------------------------------------*/
/*                                                                   */
/* REVIEWS CLOB USAGE FOR POSSIBLE CHANGE TO VARCHAR DATA TYPE       */
/*                                                                   */
/* DATE        USER      COMMENTS                                    */
/* ----------  --------  ------------------------------------------  */
/* 2017-11-27  D.ROTH    v1.0 ORIGINAL                               */
/*-------------------------------------------------------------------*/

(
        DATABASENAME (VARCHAR(128))
       ,TABLENAME    (VARCHAR(128))
)
AS
(

LOCKING ROW FOR ACCESS
SELECT DatabaseName
      ,TableName
      ,ColumnName
      ,'CLOB' DataType
      ,CASE typeofuse
         WHEN  1 THEN  '  1: Reference only'
         WHEN  2 THEN  '  2: Access'
         WHEN  3 THEN  '  3: Reference, access'         
         WHEN  6 THEN  '  6: Access, conditional'
         WHEN  7 THEN  '  7: Reference, access, conditional'
         WHEN 10 THEN  ' 10: Access, INNER JOIN'
         WHEN 14 THEN  ' 14: Access, conditional, inner join'
         WHEN 18 THEN  ' 18: Access, OUTER JOIN'
         WHEN 22 THEN  ' 22: Access, conditional, outer join' 
         WHEN 30 THEN  ' 30: Access, conditional, INNER AND OUTER JOIN' 
         WHEN 34 THEN  ' 34: Access, sum'
         WHEN 38 THEN  ' 38: Access, conditional, SUM'
         WHEN 46 THEN  ' 46: Access, conditional, sum, inner join'
         WHEN 54 THEN  ' 54: Access, conditional, SUM, OUTER JOIN'
         WHEN 70 THEN  ' 70: Access, conditional, full outer join' 
         WHEN 102 THEN '102: Access, conditional, SUM, FULL OUTER JOIN'         
         ELSE ' '
         END Type_of_Use

      ,SUM(COALESCE(freqofuse,0)) (FORMAT 'zzz,zzz,zzz') Frequency_Of_Use
      ,MAX(CAST(c.LastAlterTimeStamp  AS DATE)) LastAlterDate
      ,MAX(c.LastAlterName) LastAlterName

 FROM dbc.ColumnsV c

 LEFT OUTER JOIN BEDW.DBQL_Obj_NoWD_Archive_A_VW d
   ON d.objectdatabasename LIKE (:DATABASENAME || '%')
  AND d.objectdatabasename = c.databasename
  AND d.objecttablename    = c.tablename
  AND d.objectcolumnname   = c.columnname
  AND d.ObjectType IN ('col')
  AND d.logdate > CURRENT_DATE - 7

WHERE NOT ((c.databasename || ' ') LIKE ANY ('%_S %','%_W %','%_H %','%STG%','%SANDBOX%','%_2014%','%_2015%','%_2016%','%_QCD%','%TASD%','%_TAS_%','%_STAGE_%','%_WORK_%','%_SLIKE%','%BKUP_SL%','%_PERF_%','%_LABOR_%','%_PC_%','%_META_%','%_SECPOC_%','%_BGS_%'))
  AND NOT ((c.tablename    || ' ') LIKE ANY ('%ERR1 %','%ERR2 %','%_E1 %','%_E2 %','%_ET1 %','%_ET2 %','%_DAold %','%_OLD %','%_BACKUP %','%_BAK %','%_bkp %','%_BCK %','CORE_%','SAP_%','DEMO_%'))                      

  AND c.databasename LIKE (:DATABASENAME || '%')
  AND c.tablename    LIKE (:TABLENAME    || '%')
  AND c.ColumnType   IN ('CO')

GROUP BY 1,2,3,4,5
ORDER BY 1,2,3,4,5
;

);


COMMENT ON BEDW_${TDENV}_PERF_M.MODEL_STANDARDS_CLOB_USAGE
'v1.0 - Macro to validate if CLOBS should remaon CLOBS or be changed to VARCHAR for query processing - Updated 2017-12-05';