Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2013
    Posts
    2

    Unanswered: Update function - need your help

    Hello all,

    I really need some help for the following situation:
    -I have a view that shoes and calculates some KPIs
    -I have a function that runs based on this view and updates/inserts in a table the values.

    If i query the view directly it works - there is data calculate ( a count),but this data doesn't end up in my table..

    view name : "KPI"."DAILY_KPI_CALCULATION"
    function name : "KPI"."CALCULATE_KPI_LIS()"
    TABLE NAME : "KPI"."CALCULATED_KPIS_PER_TICKET"

    Below is the function code(that should update/insert some of the new KPIs in the table above mentioned.

    Code:
    -- Function: "KPI"."CALCULATE_KPI_LIST"()
    
    -- DROP FUNCTION "KPI"."CALCULATE_KPI_LIST"();
    
    CREATE OR REPLACE FUNCTION "KPI"."CALCULATE_KPI_LIST"()
      RETURNS integer AS
    $BODY$DECLARE
    
    
    main_cursor	CURSOR FOR SELECT "T_TID", "REPORTING_PRIORITY", "TTRCURRENT", "TTRSSPO", "TTRMCO", 
    		       "TTA_PER_TT", "TICKETLIFECYCLE", "TTASSPO", "TTAASP", "TTAPERTSSPOTEAMS", 
    		       "COUNT_OF_ACTIVATIONS", "COUNT_OF_SSPO_ACTIVATIONS", "COUNT_OF_ASP_TEAM_ACTIVATIONS", "OCEANE_PRIORITY", 
    		       "IN_GIGEL", "CONFIRMED_PRIORITY_GIGEL", "GIGEL_PRIORITY", "MTTH", "ISCALCISINSLATTASM", ttrsspoinitmco
    		  FROM "KPI"."DAILY_KPI_CALCULATION";
    
    		  
    crtrow		"KPI"."DAILY_KPI_CALCULATION"%ROWTYPE;
    
    biTID			bigint;
    cvREPORTING_PRIORITY	character varying;
    
    bINola			boolean;
    iCountOfActivations	integer;
    iCountOfSSPOActivations	integer;
    iCountOfASPActivations  integer;
    cvOceanePriority	character varying;
    bInGigel		boolean;
    bConfirmedPriorityGigel	boolean;
    cvGigelPriority		character varying;
    bInOLAsspo		boolean;
    bInOLAmco		boolean;
    dpMTTH			double precision;
    iRet 			integer;
    bInOLAsspoinitmco	boolean;
    BEGIN
    iRet = 0;
    
    OPEN main_cursor;
    	LOOP
    	FETCH main_cursor INTO crtrow;
    	IF (FOUND) THEN
    		biTID = crtrow."T_TID";
    		cvREPORTING_PRIORITY =  crtrow."REPORTING_PRIORITY";
    		bINola = "CALCULATE_IS_IN_SLA_TTR"(  crtrow."TTRCURRENT", cvREPORTING_PRIORITY);
    		iCountOfActivations = crtrow."COUNT_OF_ACTIVATIONS";
    		iCountOfSSPOActivations = crtrow."COUNT_OF_SSPO_ACTIVATIONS";
    		iCountOfASPActivations = crtrow."COUNT_OF_ASP_TEAM_ACTIVATIONS";
    		cvOceanePriority = crtrow."OCEANE_PRIORITY";
    		bInGigel =  crtrow."IN_GIGEL";
    		cvGigelPriority = crtrow."GIGEL_PRIORITY";
    		bConfirmedPriorityGigel = crtrow."CONFIRMED_PRIORITY_GIGEL";		
    		bInOLAsspo = "CALCULATE_IS_IN_SLA_TTR"(  crtrow."TTRSSPO", cvREPORTING_PRIORITY);
    		bInOLAmco = "CALCULATE_IS_IN_SLA_TTR"(  crtrow."TTRMCO", cvREPORTING_PRIORITY);
    		dpMTTH = crtrow."MTTH";
    		bInOLAsspoinitmco = "CALCULATE_IS_IN_SLA_TTR"(  crtrow.ttrsspoinitmco, cvREPORTING_PRIORITY);
    
    		IF (SELECT "A"."TID" FROM "KPI"."CALCULATED_KPIS_PER_TICKET" "A" WHERE "A"."TID" = biTID) IS NULL THEN
    			INSERT INTO "KPI"."CALCULATED_KPIS_PER_TICKET"(
    				   "TID", "IN_OLA", "COUNT_OF_ACTIVATIONS", 
    				    "COUNT_OF_SSPO_ACTIVATIONS","COUNT_OF_ASP_TEAM_ACTIVATIONS", "OCEANE_PRIORITY", "IN_GIGEL", "CONFIRMED_PRIORITY_GIGEL", 
    				    "GIGEL_PRIORITY", "IN_OLA_SSPO", "IN_SLA_TTR_SSPO_INIT_MCO","IN_OLA_MCO", "MTTH")
    			    VALUES (biTID, bINola, iCountOfActivations, iCountOfSSPOActivations, iCountOfASPActivations, cvOceanePriority, 
    				    bInGigel, bConfirmedPriorityGigel, cvGigelPriority, bInOLAsspo, bInOLAsspoinitmco, 
    				    bInOLAmco, dpMTTH);
    		ELSE
    			UPDATE "KPI"."CALCULATED_KPIS_PER_TICKET" "A"
    			   SET "TID"=biTID, "IN_OLA"= bINola, "LAST_UPDATED"=NOW()::timestamp without time zone,  
    			       "COUNT_OF_ACTIVATIONS"= iCountOfActivations, "COUNT_OF_SSPO_ACTIVATIONS"= iCountOfSSPOActivations,"COUNT_OF_ASP_TEAM_ACTIVATIONS"= iCountOfASPActivations, "OCEANE_PRIORITY"= cvOceanePriority, 
    			       "IN_GIGEL"= bInGigel, "CONFIRMED_PRIORITY_GIGEL"= bConfirmedPriorityGigel, "GIGEL_PRIORITY"= cvGigelPriority, "IN_OLA_SSPO"= bInOLAsspo, "IN_SLA_TTR_SSPO_INIT_MCO"= bInOLAsspoinitmco, "IN_OLA_MCO"=bInOLAmco, "MTTH"=dpMTTH 
    			 WHERE "A"."TID" = biTID;
    		END IF;
    		iRet = iRet + 1;
    	ELSE
    		EXIT;
    		
    
    	END IF;
    END LOOP;
    CLOSE main_cursor;
    RETURN iRet;
    END;$BODY$
      LANGUAGE plpgsql VOLATILE
      COST 100;
    ALTER FUNCTION "KPI"."CALCULATE_KPI_LIST"()
      OWNER TO postgres;
    COMMENT ON FUNCTION "KPI"."CALCULATE_KPI_LIST"() IS 'this function is executed daily (by checker_dev2.php script).To be created an sql job for it.';
    * the function runs and returns t ,but nothing is changed - or at least the columns Count remain the same - with the data previously stored.
    Thank you for all your advice's.

  2. #2
    Join Date
    Apr 2013
    Posts
    2

    solved

    Hello,

    I've managed to solve my "problem".
    The main cursor should have the specified columns in the same order as the %rowtype in order to fetch and match them accordingly.
    I've moved TTAASP at the end (has the same position as in the view).
    Things works properly now.

    I've learned something.


    Kind regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •