SQL Queries - CAST Knowledge Base - Queries on metrics and diagnostics - How to populate the working tables present in DETAIL and TOTAL procedure

 -- Function: cb820_asd_sup_local.diag_scope_jeeub001(integer, integer, integer, integer)

-- DROP FUNCTION cb820_asd_sup_local.diag_scope_jeeub001(integer, integer, integer, integer);

CREATE OR REPLACE FUNCTION cb820_asd_sup_local.diag_scope_jeeub001(i_snapshot_id integer, i_metric_parent_id integer, i_metric_id integer, i_metric_child_id integer)
  RETURNS integer AS
$BODY$
declare
	ERRORCODE	INT := 0;
	L_PARAM_NUM	INT := 0;
Begin
--<<NAME>>DIAG_SCOPE_JEEUB001<</NAME>>
--<<COMMENT>> Template name   = DSSGENERIC. <</COMMENT>>
--<<COMMENT>> Diagnostic name = persistent class method equals() and hashCode() must access its fields through getter methods. <</COMMENT>>
--<<COMMENT>> Definition      = . <</COMMENT>>
--<<COMMENT>> Action          = List all equals or hashcode methods of persistence classes that rely  on the attributes.. <</COMMENT>>
--<<COMMENT>> Value           = 1. <</COMMENT>>
   
	
    
 ERRORCODE :=  DIAG_HIBER_JAVA  (I_SNAPSHOT_ID, I_METRIC_PARENT_ID, I_METRIC_ID) ;

	
	insert into DSS_METRIC_SCOPES 
		(OBJECT_ID, METRIC_PARENT_ID, METRIC_ID, OBJECT_PARENT_ID, SNAPSHOT_ID, METRIC_NUM_VALUE, METRIC_CHAR_VALUE, METRIC_OBJECT_ID, COMPUTE_VALUE)
	Select 
		distinct P1.OBJECT_ID, I_METRIC_ID, I_METRIC_CHILD_ID, SC.MODULE_ID, I_SNAPSHOT_ID, 0, Null, 0, 0
	From 
		WK_DIA_INHPRSTCLS T1
                       join DSSAPP_MODULES SC      on T1.APPLICATION_ID	= SC.MODULE_ID
                       join DIAG_OBJECT_PARENTS P1 on T1.TARGET_OBJECT_ID = P1.PARENT_ID
					   join CDT_OBJECTS TN         on P1.OBJECT_ID  = TN.OBJECT_ID
                       join WK_DIA_INHPRSTCLS T2   on T1.HIBERNATE_ID     = T2.HIBERNATE_ID --- T1 and T2 are in the same java class.
                                                  and T1.SOURCE_OBJECT_ID = T2.SOURCE_OBJECT_ID
                                                  and T1.TARGET_OBJECT_ID = T2.TARGET_OBJECT_ID
                                                  and T1.APPLICATION_ID   = T2.APPLICATION_ID
                       join DIAG_OBJECT_PARENTS P2 on T2.TARGET_OBJECT_ID = P2.PARENT_ID
                       join DIAG_CTV_LINKS_SIMPLE IL on IL.CALLER_ID  = P1.OBJECT_ID
                                                    and IL.CALLED_ID  = P2.OBJECT_ID
                       join CTV_OBJECTS co        on co.OBJECT_ID = P2.OBJECT_ID
                                                 and ( co.OBJECT_PROP & 80 ) != 80   /* Not Like '%STATIC%FINAL%' */                             
  
	Where 
		 Not Exists 
		(
			Select 1 
			From 
				DSS_OBJECT_EXCEPTIONS E
			Where 
				E.METRIC_ID		= I_METRIC_ID 
				And E.OBJECT_ID	= P1.OBJECT_ID
		)
		
and SC.TECHNO_TYPE  = 140029 /* JEE Module */
and P1.OBJECT_TYPE IN (102,988)  -- method, gen method
and TN.OBJECT_NAME in ('equals', 'hashCode')
and P2.OBJECT_TYPE              = 103   -- field
 
		
	;
	
	  
	 
Return ERRORCODE;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION cb820_asd_sup_local.diag_scope_jeeub001(integer, integer, integer, integer)
  OWNER TO operator;

select * 
from dss_history
where LOWER(description) like '%Total_or_detail_procedure_name%'

select * 
from dss_history
where LOWER(description) like '%diag_scope_jeeub001%'Result -description;action_date;history_id"Start diag_scope_jeeub001 -3 61019 7468 2";"2017-12-05 05:50:08.785";3116

 Select Procedure_name (parameters) ;

Select DIAG_HIBER_JAVA (-3, 61019 , 7468 );

Select * from <working_table> ;