Question

2
Replies
126
Views
Pavanir6 Member since 2016 103 posts
Amazon
Posted: January 12, 2018
Last activity: January 16, 2018
Closed
Solved

Error :in database pegadata, table null has an unknown type:

I am using pgsql. I am calling a function from pega and i get the error in tracer as "

in database pegadata, table null has an unknown type:  "

My function:'

CREATE OR REPLACE FUNCTION data.global_manager_compliance(
IN start_date TEXT DEFAULT NULL,
IN end_date TEXT DEFAULT NULL) RETURNS void AS $$
DECLARE
tempquery CHARACTER VARYING(2500) DEFAULT NULL;
tempStart TIMESTAMP(6) DEFAULT TO_DATE(start_date, 'YYYYMMDD');
tempEnd TIMESTAMP(6) DEFAULT TO_DATE(end_date, 'YYYYMMDD');

BEGIN
tempquery := 'CREATE OR REPLACE VIEW DATA.GLOBAL_MANAGER_COMPLIANCE_VIEW ( TASKGROUPKEY, TOTALCOUNT, RESOLVEDONTIMECOUNT, WITHDRAWNCOUNT, OPENTASKSCOUNT, COMPLIANCEPERCENTAGE) AS
select TaskGroupKey,
count(pyid)as TotalCount, count(case when pystatuswork=''Resolved-Completed'' then 1 end) as Count_Tasks_Completed_OnTime,
count(case when pystatuswork=''Resolved-Withdrawn'' then 1 end) as Withdrawntasks,
(count(pyID)-(count(case when pystatuswork=''Resolved-Withdrawn'' then 1 end) )) as Total_Tasks_Assigned,
case when (count(pyID)-(count(case when pystatuswork=''Resolved-Withdrawn'' then 1 end))) >0 then
((count(case when pystatuswork=''Resolved-Completed'' then 1 end))/(count(pyID)-(count(case when pystatuswork=''Resolved-Withdrawn'' then 1 end))))*100 else 0 end as CompliancePercentage
from DATA.AMZ_PAYWB_RYTM_WORK WORK
where ( TaskGroupKey is not null AND date(TASKENDDATETIME)>='''||tempStart||''' AND date(TASKENDDATETIME)<='''||tempEnd||''' AND pyid like ''TSK-%'' and taskassignedto is not null and (IsDIFTask is null or IsDIFTask!=''true''))

GROUP BY TaskGroupKey';
RAISE NOTICE '%', tempquery;
execute tempquery ;
END
$$
LANGUAGE plpgsql;

***Updated by moderator: Lochan to add Categories***

Data Integration System Administration
Moderation Team has archived post
Share this page LinkedIn