Good day,
I jsut noticed, everytime i do a database restore and run the analytics, i am getting this error;
> 2025-03-13 23:57:10 Analytics table update process
> 2025-03-13 23:57:10 Drop SQL views
> 2025-03-13 23:57:10 0 successful and 0 failed items
> 2025-03-13 23:57:10 Generating resource tables
> 2025-03-13 23:57:53 PreparedStatementCallback; SQL [insert into _dateperiodstructure_temp values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)]; ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey1" Detail: Key (dateperiod)=(2037-01-01) already exists.; nested exception is org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint "_dateperiodstructure_temp_pkey1" Detail: Key (dateperiod)=(2037-01-01) already exists.
> 2025-03-13 23:57:53 skipped stage. 9 successful and 1 failed items
> 2025-03-13 23:57:53 Create SQL views
> 2025-03-13 23:57:53 0 successful and 0 failed items
> 2025-03-13 23:57:53 Validating Analytics Table DATA_VALUE
> 2025-03-13 23:57:53 No data values exist, not updating aggregate analytics tables
> 2025-03-13 23:57:53 Validating Analytics Table COMPLETENESS
> 2025-03-13 23:57:53 No complete registrations exist, not updating completeness analytics tables
> 2025-03-13 23:57:53 Validating Analytics Table COMPLETENESS_TARGET
> 2025-03-13 23:57:53 Performing pre-create table work
> 2025-03-13 23:57:53 Dropping temp tables (if any) COMPLETENESS_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Creating analytics tables COMPLETENESS_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Populating analytics tables COMPLETENESS_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Invoking analytics table hooks COMPLETENESS_TARGET
> 2025-03-13 23:57:53 Creating indexes COMPLETENESS_TARGET
> 2025-03-13 23:57:53 Analyzing analytics tables COMPLETENESS_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Drop SQL views
> 2025-03-13 23:57:53 0 successful and 0 failed items
> 2025-03-13 23:57:53 Swapping analytics tables COMPLETENESS_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Create SQL views
> 2025-03-13 23:57:53 0 successful and 0 failed items
> 2025-03-13 23:57:53 Validating Analytics Table ORG_UNIT_TARGET
> 2025-03-13 23:57:53 Performing pre-create table work
> 2025-03-13 23:57:53 Dropping temp tables (if any) ORG_UNIT_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Creating analytics tables ORG_UNIT_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Populating analytics tables ORG_UNIT_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Invoking analytics table hooks ORG_UNIT_TARGET
> 2025-03-13 23:57:53 Creating indexes ORG_UNIT_TARGET
> 2025-03-13 23:57:53 Analyzing analytics tables ORG_UNIT_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Drop SQL views
> 2025-03-13 23:57:53 0 successful and 0 failed items
> 2025-03-13 23:57:53 Swapping analytics tables ORG_UNIT_TARGET
> 2025-03-13 23:57:53 1 successful and 0 failed items
> 2025-03-13 23:57:53 Create SQL views
> 2025-03-13 23:57:53 0 successful and 0 failed items
> 2025-03-13 23:57:54 Validating Analytics Table OWNERSHIP
> 2025-03-13 23:57:54 Performing pre-create table work
> 2025-03-13 23:57:54 Dropping temp tables (if any) OWNERSHIP
> 2025-03-13 23:57:54 3 successful and 0 failed items
> 2025-03-13 23:57:54 Creating analytics tables OWNERSHIP
> 2025-03-13 23:57:54 3 successful and 0 failed items
> 2025-03-13 23:57:54 Populating analytics tables OWNERSHIP
> 2025-03-13 23:57:54 Invoking analytics table hooks OWNERSHIP
> 2025-03-13 23:57:54 Creating indexes OWNERSHIP
> 2025-03-13 23:57:54 Analyzing analytics tables OWNERSHIP
> 2025-03-13 23:57:54 3 successful and 0 failed items
> 2025-03-13 23:57:54 Drop SQL views
> 2025-03-13 23:57:54 0 successful and 0 failed items
> 2025-03-13 23:57:54 Swapping analytics tables OWNERSHIP
> 2025-03-13 23:57:54 3 successful and 0 failed items
> 2025-03-13 23:57:54 Create SQL views
> 2025-03-13 23:57:54 0 successful and 0 failed items
> 2025-03-13 23:57:54 Validating Analytics Table EVENT
> 2025-03-13 23:57:57 Performing pre-create table work
> 2025-03-13 23:57:57 Dropping temp tables (if any) EVENT
> 2025-03-13 23:57:57 3 successful and 0 failed items
> 2025-03-13 23:57:57 Creating analytics tables EVENT
> 2025-03-13 23:57:58 3 successful and 0 failed items
> 2025-03-13 23:57:58 Populating analytics tables EVENT
> 2025-03-13 23:57:58 StatementCallback; bad SQL grammar [insert into analytics_event_temp_wppq4pp4e0e_2025 ("uidlevel1","uidlevel2","uidlevel3","uidlevel4","U1Ycp3x0I8e","j2iYKiMubNc","daily","weekly","weeklywednesday","weeklythursday","weeklysaturday","weeklysunday","biweekly","monthly","bimonthly","quarterly","quarterlynov","sixmonthly","sixmonthlyapril","sixmonthlynov","yearly","financialapril","financialjuly","financialoct","financialnov","SdZeztIAlYe","bIuoAJiBGOO","Sop8h9c9Aqr","u7oOjeeufXh","ovJJmGxWOyU","sWehkELwWO4","dLAxEJwSbwG","hRtPFJKNpyP","pzysdfPpy4q","ONMzwVQRcej","CW1y4QbjbEl","mlFwKT2s9P3","UFGy0QELNf6","CnhzQe0f9SY","QORsebUaDFq","O7QjEAHZPn3","Cp8ZRcXx9JX","Igt0ywGtdPk","feGaoAXJpBe","ObUrVfFdJcm","xnqjFmJPJR5","bIDQYrfTOOw","vfKSn222DRk","eavL12NTJ3V","XbdlwzSyAm9","EfL6HSyDoGs","MAlwFxkVuwY","XpryiPbRdZD","oA4YEKSmjKA","psi","pi","ps","ao","enrollmentdate","incidentdate","executiondate","duedate","completeddate","created","lastupdated","storedby","createdbyusername","createdbyname","createdbylastname","createdbydisplayname","lastupdatedbyusername","lastupdatedbyname","lastupdatedbylastname","lastupdatedbydisplayname","pistatus","psistatus","psigeometry","longitude","latitude","ou","ouname","oucode","oulevel","ougeometry","pigeometry","registrationou","enrollmentou","tei","teigeometry") select ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ougs."U1Ycp3x0I8e",ougs."j2iYKiMubNc",dps."daily",dps."weekly",dps."weeklywednesday",dps."weeklythursday",dps."weeklysaturday",dps."weeklysunday",dps."biweekly",dps."monthly",dps."bimonthly",dps."quarterly",dps."quarterlynov",dps."sixmonthly",dps."sixmonthlyapril",dps."sixmonthlynov",dps."yearly",dps."financialapril",dps."financialjuly",dps."financialoct",dps."financialnov",(select cast(eventdatavalues #>> '{SdZeztIAlYe, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{SdZeztIAlYe,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "SdZeztIAlYe",(select cast(eventdatavalues #>> '{bIuoAJiBGOO, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{bIuoAJiBGOO,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "bIuoAJiBGOO",(select cast(eventdatavalues #>> '{Sop8h9c9Aqr, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Sop8h9c9Aqr,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Sop8h9c9Aqr",(select cast(eventdatavalues #>> '{u7oOjeeufXh, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{u7oOjeeufXh,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "u7oOjeeufXh",(select cast(eventdatavalues #>> '{ovJJmGxWOyU, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ovJJmGxWOyU,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ovJJmGxWOyU",(select cast(eventdatavalues #>> '{sWehkELwWO4, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{sWehkELwWO4,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "sWehkELwWO4",(select cast(eventdatavalues #>> '{dLAxEJwSbwG, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{dLAxEJwSbwG,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "dLAxEJwSbwG",(select cast(eventdatavalues #>> '{hRtPFJKNpyP, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{hRtPFJKNpyP,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "hRtPFJKNpyP",(select cast(eventdatavalues #>> '{pzysdfPpy4q, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{pzysdfPpy4q,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "pzysdfPpy4q",(select cast(eventdatavalues #>> '{ONMzwVQRcej, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ONMzwVQRcej,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ONMzwVQRcej",(select cast(eventdatavalues #>> '{CW1y4QbjbEl, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CW1y4QbjbEl,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CW1y4QbjbEl",(select cast(eventdatavalues #>> '{mlFwKT2s9P3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{mlFwKT2s9P3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "mlFwKT2s9P3",(select cast(eventdatavalues #>> '{UFGy0QELNf6, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{UFGy0QELNf6,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "UFGy0QELNf6",(select cast(eventdatavalues #>> '{CnhzQe0f9SY, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CnhzQe0f9SY,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CnhzQe0f9SY",(select cast(eventdatavalues #>> '{QORsebUaDFq, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{QORsebUaDFq,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "QORsebUaDFq",(select cast(eventdatavalues #>> '{O7QjEAHZPn3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{O7QjEAHZPn3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "O7QjEAHZPn3",(select cast(eventdatavalues #>> '{Cp8ZRcXx9JX, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Cp8ZRcXx9JX,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Cp8ZRcXx9JX",(select cast(eventdatavalues #>> '{Igt0ywGtdPk, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Igt0ywGtdPk,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Igt0ywGtdPk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4626) as "feGaoAXJpBe",(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4627 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$') as "ObUrVfFdJcm",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1051) as "xnqjFmJPJR5",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4628) as "bIDQYrfTOOw",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4629) as "vfKSn222DRk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4630) as "eavL12NTJ3V",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4855) as "XbdlwzSyAm9",(select cast(value as bigint) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4637 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "EfL6HSyDoGs",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4645) as "MAlwFxkVuwY",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4646) as "XpryiPbRdZD",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4647) as "oA4YEKSmjKA",psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,psi.created,psi.lastupdated,psi.storedby,psi.createdbyuserinfo ->> 'username' as createdbyusername,psi.createdbyuserinfo ->> 'firstName' as createdbyname,psi.createdbyuserinfo ->> 'surname' as createdbylastname,case when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then trim(psi.createdbyuserinfo ->> 'username') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'firstName') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'surname') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName')) when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') else concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') end as createdbydisplayname,psi.lastupdatedbyuserinfo ->> 'username' as lastupdatedbyusername,psi.lastupdatedbyuserinfo ->> 'firstName' as lastupdatedbyname,psi.lastupdatedbyuserinfo ->> 'surname' as lastupdatedbylastname,case when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then trim(psi.lastupdatedbyuserinfo ->> 'username') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'firstName') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'surname') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName')) when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') else concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') end as lastupdatedbydisplayname,pi.status,psi.status,psi.geometry,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,coalesce(registrationou.uid,ou.uid),coalesce(enrollmentou.uid,ou.uid),tei.uid,tei.geometry from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false left join organisationunit registrationou on tei.organisationunitid=registrationou.organisationunitid inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc('month', CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) as date)=ougs.startdate or ougs.startdate is null) left join organisationunit enrollmentou on pi.organisationunitid=enrollmentou.organisationunitid inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END as date)=dps.dateperiod where psi.lastupdated < '2025-03-13T23:57:10' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) >= '2025-01-01T00:00:00' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) < '2026-01-01T00:00:00' and pr.programid=4687 and psi.organisationunitid is not null and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) is not null and dps.year >= 1975 and dps.year <= 2050 and psi.status in ('COMPLETED','ACTIVE','SCHEDULE')and psi.deleted is false ]; nested exception is org.postgresql.util.PSQLException: ERROR: column dps.quarterlynov does not exist Hint: Perhaps you meant to reference the column "dps.quarterly". Position: 1551
> 2025-03-13 23:57:58 StatementCallback; bad SQL grammar [insert into analytics_event_temp_wppq4pp4e0e_1997 ("uidlevel1","uidlevel2","uidlevel3","uidlevel4","U1Ycp3x0I8e","j2iYKiMubNc","daily","weekly","weeklywednesday","weeklythursday","weeklysaturday","weeklysunday","biweekly","monthly","bimonthly","quarterly","quarterlynov","sixmonthly","sixmonthlyapril","sixmonthlynov","yearly","financialapril","financialjuly","financialoct","financialnov","SdZeztIAlYe","bIuoAJiBGOO","Sop8h9c9Aqr","u7oOjeeufXh","ovJJmGxWOyU","sWehkELwWO4","dLAxEJwSbwG","hRtPFJKNpyP","pzysdfPpy4q","ONMzwVQRcej","CW1y4QbjbEl","mlFwKT2s9P3","UFGy0QELNf6","CnhzQe0f9SY","QORsebUaDFq","O7QjEAHZPn3","Cp8ZRcXx9JX","Igt0ywGtdPk","feGaoAXJpBe","ObUrVfFdJcm","xnqjFmJPJR5","bIDQYrfTOOw","vfKSn222DRk","eavL12NTJ3V","XbdlwzSyAm9","EfL6HSyDoGs","MAlwFxkVuwY","XpryiPbRdZD","oA4YEKSmjKA","psi","pi","ps","ao","enrollmentdate","incidentdate","executiondate","duedate","completeddate","created","lastupdated","storedby","createdbyusername","createdbyname","createdbylastname","createdbydisplayname","lastupdatedbyusername","lastupdatedbyname","lastupdatedbylastname","lastupdatedbydisplayname","pistatus","psistatus","psigeometry","longitude","latitude","ou","ouname","oucode","oulevel","ougeometry","pigeometry","registrationou","enrollmentou","tei","teigeometry") select ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ougs."U1Ycp3x0I8e",ougs."j2iYKiMubNc",dps."daily",dps."weekly",dps."weeklywednesday",dps."weeklythursday",dps."weeklysaturday",dps."weeklysunday",dps."biweekly",dps."monthly",dps."bimonthly",dps."quarterly",dps."quarterlynov",dps."sixmonthly",dps."sixmonthlyapril",dps."sixmonthlynov",dps."yearly",dps."financialapril",dps."financialjuly",dps."financialoct",dps."financialnov",(select cast(eventdatavalues #>> '{SdZeztIAlYe, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{SdZeztIAlYe,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "SdZeztIAlYe",(select cast(eventdatavalues #>> '{bIuoAJiBGOO, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{bIuoAJiBGOO,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "bIuoAJiBGOO",(select cast(eventdatavalues #>> '{Sop8h9c9Aqr, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Sop8h9c9Aqr,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Sop8h9c9Aqr",(select cast(eventdatavalues #>> '{u7oOjeeufXh, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{u7oOjeeufXh,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "u7oOjeeufXh",(select cast(eventdatavalues #>> '{ovJJmGxWOyU, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ovJJmGxWOyU,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ovJJmGxWOyU",(select cast(eventdatavalues #>> '{sWehkELwWO4, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{sWehkELwWO4,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "sWehkELwWO4",(select cast(eventdatavalues #>> '{dLAxEJwSbwG, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{dLAxEJwSbwG,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "dLAxEJwSbwG",(select cast(eventdatavalues #>> '{hRtPFJKNpyP, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{hRtPFJKNpyP,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "hRtPFJKNpyP",(select cast(eventdatavalues #>> '{pzysdfPpy4q, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{pzysdfPpy4q,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "pzysdfPpy4q",(select cast(eventdatavalues #>> '{ONMzwVQRcej, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ONMzwVQRcej,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ONMzwVQRcej",(select cast(eventdatavalues #>> '{CW1y4QbjbEl, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CW1y4QbjbEl,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CW1y4QbjbEl",(select cast(eventdatavalues #>> '{mlFwKT2s9P3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{mlFwKT2s9P3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "mlFwKT2s9P3",(select cast(eventdatavalues #>> '{UFGy0QELNf6, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{UFGy0QELNf6,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "UFGy0QELNf6",(select cast(eventdatavalues #>> '{CnhzQe0f9SY, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CnhzQe0f9SY,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CnhzQe0f9SY",(select cast(eventdatavalues #>> '{QORsebUaDFq, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{QORsebUaDFq,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "QORsebUaDFq",(select cast(eventdatavalues #>> '{O7QjEAHZPn3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{O7QjEAHZPn3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "O7QjEAHZPn3",(select cast(eventdatavalues #>> '{Cp8ZRcXx9JX, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Cp8ZRcXx9JX,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Cp8ZRcXx9JX",(select cast(eventdatavalues #>> '{Igt0ywGtdPk, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Igt0ywGtdPk,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Igt0ywGtdPk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4626) as "feGaoAXJpBe",(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4627 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$') as "ObUrVfFdJcm",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1051) as "xnqjFmJPJR5",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4628) as "bIDQYrfTOOw",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4629) as "vfKSn222DRk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4630) as "eavL12NTJ3V",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4855) as "XbdlwzSyAm9",(select cast(value as bigint) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4637 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "EfL6HSyDoGs",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4645) as "MAlwFxkVuwY",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4646) as "XpryiPbRdZD",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4647) as "oA4YEKSmjKA",psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,psi.created,psi.lastupdated,psi.storedby,psi.createdbyuserinfo ->> 'username' as createdbyusername,psi.createdbyuserinfo ->> 'firstName' as createdbyname,psi.createdbyuserinfo ->> 'surname' as createdbylastname,case when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then trim(psi.createdbyuserinfo ->> 'username') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'firstName') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'surname') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName')) when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') else concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') end as createdbydisplayname,psi.lastupdatedbyuserinfo ->> 'username' as lastupdatedbyusername,psi.lastupdatedbyuserinfo ->> 'firstName' as lastupdatedbyname,psi.lastupdatedbyuserinfo ->> 'surname' as lastupdatedbylastname,case when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then trim(psi.lastupdatedbyuserinfo ->> 'username') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'firstName') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'surname') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName')) when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') else concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') end as lastupdatedbydisplayname,pi.status,psi.status,psi.geometry,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,coalesce(registrationou.uid,ou.uid),coalesce(enrollmentou.uid,ou.uid),tei.uid,tei.geometry from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false left join organisationunit registrationou on tei.organisationunitid=registrationou.organisationunitid inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc('month', CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) as date)=ougs.startdate or ougs.startdate is null) left join organisationunit enrollmentou on pi.organisationunitid=enrollmentou.organisationunitid inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END as date)=dps.dateperiod where psi.lastupdated < '2025-03-13T23:57:10' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) >= '1997-01-01T00:00:00' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) < '1998-01-01T00:00:00' and pr.programid=4687 and psi.organisationunitid is not null and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) is not null and dps.year >= 1975 and dps.year <= 2050 and psi.status in ('COMPLETED','ACTIVE','SCHEDULE')and psi.deleted is false ]; nested exception is org.postgresql.util.PSQLException: ERROR: column dps.quarterlynov does not exist Hint: Perhaps you meant to reference the column "dps.quarterly". Position: 1551
> 2025-03-13 23:57:58 StatementCallback; bad SQL grammar [insert into analytics_event_temp_wppq4pp4e0e_1985 ("uidlevel1","uidlevel2","uidlevel3","uidlevel4","U1Ycp3x0I8e","j2iYKiMubNc","daily","weekly","weeklywednesday","weeklythursday","weeklysaturday","weeklysunday","biweekly","monthly","bimonthly","quarterly","quarterlynov","sixmonthly","sixmonthlyapril","sixmonthlynov","yearly","financialapril","financialjuly","financialoct","financialnov","SdZeztIAlYe","bIuoAJiBGOO","Sop8h9c9Aqr","u7oOjeeufXh","ovJJmGxWOyU","sWehkELwWO4","dLAxEJwSbwG","hRtPFJKNpyP","pzysdfPpy4q","ONMzwVQRcej","CW1y4QbjbEl","mlFwKT2s9P3","UFGy0QELNf6","CnhzQe0f9SY","QORsebUaDFq","O7QjEAHZPn3","Cp8ZRcXx9JX","Igt0ywGtdPk","feGaoAXJpBe","ObUrVfFdJcm","xnqjFmJPJR5","bIDQYrfTOOw","vfKSn222DRk","eavL12NTJ3V","XbdlwzSyAm9","EfL6HSyDoGs","MAlwFxkVuwY","XpryiPbRdZD","oA4YEKSmjKA","psi","pi","ps","ao","enrollmentdate","incidentdate","executiondate","duedate","completeddate","created","lastupdated","storedby","createdbyusername","createdbyname","createdbylastname","createdbydisplayname","lastupdatedbyusername","lastupdatedbyname","lastupdatedbylastname","lastupdatedbydisplayname","pistatus","psistatus","psigeometry","longitude","latitude","ou","ouname","oucode","oulevel","ougeometry","pigeometry","registrationou","enrollmentou","tei","teigeometry") select ous."uidlevel1",ous."uidlevel2",ous."uidlevel3",ous."uidlevel4",ougs."U1Ycp3x0I8e",ougs."j2iYKiMubNc",dps."daily",dps."weekly",dps."weeklywednesday",dps."weeklythursday",dps."weeklysaturday",dps."weeklysunday",dps."biweekly",dps."monthly",dps."bimonthly",dps."quarterly",dps."quarterlynov",dps."sixmonthly",dps."sixmonthlyapril",dps."sixmonthlynov",dps."yearly",dps."financialapril",dps."financialjuly",dps."financialoct",dps."financialnov",(select cast(eventdatavalues #>> '{SdZeztIAlYe, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{SdZeztIAlYe,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "SdZeztIAlYe",(select cast(eventdatavalues #>> '{bIuoAJiBGOO, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{bIuoAJiBGOO,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "bIuoAJiBGOO",(select cast(eventdatavalues #>> '{Sop8h9c9Aqr, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Sop8h9c9Aqr,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Sop8h9c9Aqr",(select cast(eventdatavalues #>> '{u7oOjeeufXh, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{u7oOjeeufXh,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "u7oOjeeufXh",(select cast(eventdatavalues #>> '{ovJJmGxWOyU, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ovJJmGxWOyU,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ovJJmGxWOyU",(select cast(eventdatavalues #>> '{sWehkELwWO4, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{sWehkELwWO4,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "sWehkELwWO4",(select cast(eventdatavalues #>> '{dLAxEJwSbwG, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{dLAxEJwSbwG,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "dLAxEJwSbwG",(select cast(eventdatavalues #>> '{hRtPFJKNpyP, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{hRtPFJKNpyP,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "hRtPFJKNpyP",(select cast(eventdatavalues #>> '{pzysdfPpy4q, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{pzysdfPpy4q,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "pzysdfPpy4q",(select cast(eventdatavalues #>> '{ONMzwVQRcej, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{ONMzwVQRcej,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "ONMzwVQRcej",(select cast(eventdatavalues #>> '{CW1y4QbjbEl, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CW1y4QbjbEl,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CW1y4QbjbEl",(select cast(eventdatavalues #>> '{mlFwKT2s9P3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{mlFwKT2s9P3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "mlFwKT2s9P3",(select cast(eventdatavalues #>> '{UFGy0QELNf6, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{UFGy0QELNf6,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "UFGy0QELNf6",(select cast(eventdatavalues #>> '{CnhzQe0f9SY, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{CnhzQe0f9SY,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "CnhzQe0f9SY",(select cast(eventdatavalues #>> '{QORsebUaDFq, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{QORsebUaDFq,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "QORsebUaDFq",(select cast(eventdatavalues #>> '{O7QjEAHZPn3, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{O7QjEAHZPn3,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "O7QjEAHZPn3",(select cast(eventdatavalues #>> '{Cp8ZRcXx9JX, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Cp8ZRcXx9JX,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Cp8ZRcXx9JX",(select cast(eventdatavalues #>> '{Igt0ywGtdPk, value}' as bigint) from programstageinstance where programstageinstanceid=psi.programstageinstanceid and eventdatavalues #>> '{Igt0ywGtdPk,value}' ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "Igt0ywGtdPk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4626) as "feGaoAXJpBe",(select cast(value as timestamp) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4627 and value ~* '^\d{4}-\d{2}-\d{2}(\s|T)?((\d{2}:)(\d{2}:)?(\d{2}))?(|.(\d{3})|.(\d{3})Z)?$') as "ObUrVfFdJcm",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=1051) as "xnqjFmJPJR5",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4628) as "bIDQYrfTOOw",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4629) as "vfKSn222DRk",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4630) as "eavL12NTJ3V",(select value from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4855) as "XbdlwzSyAm9",(select cast(value as bigint) from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4637 and value ~* '^(-?[0-9]+)(\.[0-9]+)?$') as "EfL6HSyDoGs",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4645) as "MAlwFxkVuwY",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4646) as "XpryiPbRdZD",(select case when value = 'true' then 1 when value = 'false' then 0 else null end from trackedentityattributevalue where trackedentityinstanceid=pi.trackedentityinstanceid and trackedentityattributeid=4647) as "oA4YEKSmjKA",psi.uid,pi.uid,ps.uid,ao.uid,pi.enrollmentdate,pi.incidentdate,psi.executiondate,psi.duedate,psi.completeddate,psi.created,psi.lastupdated,psi.storedby,psi.createdbyuserinfo ->> 'username' as createdbyusername,psi.createdbyuserinfo ->> 'firstName' as createdbyname,psi.createdbyuserinfo ->> 'surname' as createdbylastname,case when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then trim(psi.createdbyuserinfo ->> 'username') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'firstName') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then trim(psi.createdbyuserinfo ->> 'surname') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName')) when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.createdbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.createdbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.createdbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.createdbyuserinfo ->> 'surname'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') else concat(trim(psi.createdbyuserinfo ->> 'surname'), ', ', trim(psi.createdbyuserinfo ->> 'firstName'), ' (', trim(psi.createdbyuserinfo ->> 'username'), ')') end as createdbydisplayname,psi.lastupdatedbyuserinfo ->> 'username' as lastupdatedbyusername,psi.lastupdatedbyuserinfo ->> 'firstName' as lastupdatedbyname,psi.lastupdatedbyuserinfo ->> 'surname' as lastupdatedbylastname,case when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then null when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then trim(psi.lastupdatedbyuserinfo ->> 'username') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'firstName') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then trim(psi.lastupdatedbyuserinfo ->> 'surname') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') = '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName')) when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') when coalesce(trim(psi.lastupdatedbyuserinfo ->> 'surname'), '') <> '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'firstName'), '') = '' and coalesce(trim(psi.lastupdatedbyuserinfo ->> 'username'), '') <> '' then concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') else concat(trim(psi.lastupdatedbyuserinfo ->> 'surname'), ', ', trim(psi.lastupdatedbyuserinfo ->> 'firstName'), ' (', trim(psi.lastupdatedbyuserinfo ->> 'username'), ')') end as lastupdatedbydisplayname,pi.status,psi.status,psi.geometry,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_X(psi.geometry) ELSE null END,CASE WHEN 'POINT' = GeometryType(psi.geometry) THEN ST_Y(psi.geometry) ELSE null END,ou.uid,ou.name,ou.code,ous.level,ou.geometry,pi.geometry,coalesce(registrationou.uid,ou.uid),coalesce(enrollmentou.uid,ou.uid),tei.uid,tei.geometry from programstageinstance psi inner join programinstance pi on psi.programinstanceid=pi.programinstanceid inner join programstage ps on psi.programstageid=ps.programstageid inner join program pr on pi.programid=pr.programid and pi.deleted is false inner join categoryoptioncombo ao on psi.attributeoptioncomboid=ao.categoryoptioncomboid left join trackedentityinstance tei on pi.trackedentityinstanceid=tei.trackedentityinstanceid and tei.deleted is false left join organisationunit registrationou on tei.organisationunitid=registrationou.organisationunitid inner join organisationunit ou on psi.organisationunitid=ou.organisationunitid left join _orgunitstructure ous on psi.organisationunitid=ous.organisationunitid left join _organisationunitgroupsetstructure ougs on psi.organisationunitid=ougs.organisationunitid and (cast(date_trunc('month', CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) as date)=ougs.startdate or ougs.startdate is null) left join organisationunit enrollmentou on pi.organisationunitid=enrollmentou.organisationunitid inner join _categorystructure acs on psi.attributeoptioncomboid=acs.categoryoptioncomboid left join _dateperiodstructure dps on cast(CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END as date)=dps.dateperiod where psi.lastupdated < '2025-03-13T23:57:10' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) >= '1985-01-01T00:00:00' and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) < '1986-01-01T00:00:00' and pr.programid=4687 and psi.organisationunitid is not null and (CASE WHEN 'SCHEDULE' = psi.status THEN psi.duedate ELSE psi.executiondate END) is not null and dps.year >= 1975 and dps.year <= 2050 and psi.status in ('COMPLETED','ACTIVE','SCHEDULE')and psi.deleted is false ]; nested exception is org.postgresql.util.PSQLException: ERROR: column dps.quarterlynov does not exist Hint: Perhaps you meant to reference the column "dps.quarterly". Position: 1551
> 2025-03-13 23:57:59 processing aborted: skipped stage, failing item caused abort. parallel processing aborted after 0 successful and 3 failed items
> 2025-03-13 23:57:59 Process failed: java.util.concurrent.CancellationException
Web API Browse it here Current user Jones_Likoro Version 2.39.9 Build revision 75f6d99 Build date March 11, 2025 at 08:16 Jasper reports version 6.20.0 User agent Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/134.0.0.0 Safari/537.36 Server date March 13, 2025 at 23:53 Last analytics table generation March 11, 2025 at 07:00 Time since last analytics table generation 64 h, 53 m, 22 s Last analytics table runtime 00:02:50.498 Environment variable DHIS2_HOME System ID 147244ff-f622-4d07-8e55-da86a255b5aa Last monitoring success Never External configuration directory /opt/dhis2 File store provider filesystem Java opts -Djava.awt.headless=true -Xmx8G -Xms8G -Dlog4j2.formatMsgNoLookups=true -Djava.security.egd=file:/dev/./urandom -XX:+UseG1GC -XX:MaxGCPauseMillis=1500 -XX:GCTimeRatio=9 -javaagent:/opt/glowroot/glowroot.jar -Djdk.tls.ephemeralDHKeySize=2048 -Djava.protocol.handler.pkgs=org.apache.catalina.webresources -Dorg.apache.catalina.security.SecurityListener.UMASK=0027 Java version 11.0.26 Java vendor Ubuntu OS name Linux OS architecture amd64 OS version 5.15.0-134-generic Memory info Mem Total in JVM: 8192 Free in JVM: 4699 Max Limit: 8192 CPU cores 4 Calendar iso8601