Analytics Error: duplicate key value violates unique constraint & Also (data (before the update) is not showing in the data entry app (and data after update is visible but is not showing in the data visualiser-pivot table report app)

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

Logs
journalctl.docx (169.1 KB)

Hi @joneslikoro

@tuzoengelbert had reported a similar issue and it looks like he found a solution:

@tuzo engelbert has confirmed that this specific issue was only found in the followed java version
java -version
openjdk version “1.8.0_292”
OpenJDK Runtime Environment (build 1.8.0_292-8u292-b10-0ubuntu1~18.04-b10)
OpenJDK 64-Bit Server VM (build 25.292-b10, mixed mode)
On changing the jre version to 8u131, the issue no longer arises.
(source cc: @ameen)

@ataallah seems to have reported a similar issue, was it resolved?

@joneslikoro, and anyone facing a similar issue, would you kindly share the specifications of your implementation (specifically, dhis2 instance version, tomcat/postgresql/java/OS versions)?

Thank you all!

i have notice something a bit odd in the databases;
the table names have changed, so when its being referenced and cant be found, then i think is causing the issues;

example;

May 02 10:14:59 core tomcat9[211284]: * INFO  2025-05-02T10:14:59,104 Create SQL views (NotificationLoggerUtil.java [taskScheduler-9]) UID:S2d15r56kzs
May 02 10:14:59 core tomcat9[211284]: * INFO  2025-05-02T10:14:59,106 [RESOURCE_TABLE S2d15r56kzs] Stage started: Create SQL views (ControlledJobProgress.java [taskScheduler-9]) UID:S2d15r56kzs
May 02 10:14:59 core tomcat9[211284]: * WARN  2025-05-02T10:14:59,467 Validation failed for SQL view 'o3Imkxa57NC' with code: 'E4310' and message: 'SQL query contains references to protected tables' (Defaul
tSqlViewService.java [taskScheduler-9]) UID:S2d15r56kzs
May 02 10:14:59 core tomcat9[211284]: * WARN  2025-05-02T10:14:59,468 Ignoring SQL view which failed validation: o3Imkxa57NC, users, message: SQL query contains references to protected tables (DefaultResour
ceTableService.java [taskScheduler-9]) UID:S2d15r56kzs

as you can see above, its trying to access table called users, while this table name has changed to userinfo, and the error its providing is totally different, as it says its protected…

thanks for that;
dhis2 instance version =2.40.7.1
tomcat =9.0.58
postgresql=12
java= 11.0.26
OS versions= 5.15.0-136-generic | Ubuntu 22.04.5 LTS

Java opts -Djava.awt.headless=true -Xmx16G -Xms16G -Duser.timezone=Africa/Windhoek -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

im sorry for adding this a bit late; the data in the data entry app is not showing and we noticed that we have similar data that have 2 different attributeoptioncomboid example the 1714593 & 15, currently trying to clean up the data via sql, will update in the due coursed.

Here is the script i used to solve the issue;

/*select *
from categoryoptioncombo
where categoryoptioncomboid in (15,1714593)
;
*/
---- data with different attributeoption

with data1714593
AS(
	SELECT *
	from datavalue
	where attributeoptioncomboid = 1714593

),
data15
AS(
	SELECT *
	from datavalue
	where attributeoptioncomboid = 15

)
,
combine
AS(
SELECT d1.dataelementid
	, de.name as dataelement_name
	, d1.periodid
	, startdate
	, enddate
	, d1.sourceid
	, ou.name as ou_name
	, d1.categoryoptioncomboid
	, d1.value as value_1714593
	, d1.lastupdated as lastupdated_1714593
	, d2.value as value_15
	, d2.lastupdated as lastupdated_15
--INTO zz_datavalue_duplicates_defaults
from data1714593 d1
 join data15 d2 on d1.dataelementid = d2.dataelementid
				AND d1.periodid = d2.periodid
				AND d1.sourceid = d2.sourceid
				AND d1.categoryoptioncomboid = d2.categoryoptioncomboid
				--AND attributeoptioncomboid
JOIn dataelement de on de.dataelementid = d1.dataelementid
JOIN organisationunit ou on ou.organisationunitid = d1.sourceid
jOIN period  pe on pe.periodid = d1.periodid
--WHERE  d1.value<> d2.value
--and d1.lastupdated < d2.lastupdated
)
DELETE
FROM datavalue d1
USING combine d2
WHERE d1.dataelementid = d2.dataelementid
	AND d1.periodid = d2.periodid
	AND d1.sourceid = d2.sourceid
	AND d1.categoryoptioncomboid = d2.categoryoptioncomboid
	AND attributeoptioncomboid = 15

;
-- Update records with the correct attribute combo
UPDATE datavalue
SET attributeoptioncomboid = 15
WHERE attributeoptioncomboid = 1714593
;


---- data with different categoryoptioncombo
with data1714593
AS(
	SELECT *
	from datavalue
	where categoryoptioncomboid = 1714593

),
data15
AS(
	SELECT *
	from datavalue
	where categoryoptioncomboid = 15

)
,
combine
AS(
	SELECT d1.dataelementid, d1.periodid, d1.sourceid, d1.attributeoptioncomboid, d1.value, d2.value
	from data1714593 d1
	join data15 d2 on d1.dataelementid = d2.dataelementid
					AND d1.periodid = d2.periodid
					AND d1.sourceid = d2.sourceid
					AND d1.attributeoptioncomboid = d2.attributeoptioncomboid
					--AND attributeoptioncomboid
	--WHERE d1.value<> d2.value
)

DELETE
FROM datavalue d1
USING combine d2
WHERE d1.dataelementid = d2.dataelementid
	AND d1.periodid = d2.periodid
	AND d1.sourceid = d2.sourceid
	AND d1.attributeoptioncomboid = d2.attributeoptioncomboid
	AND categoryoptioncomboid = 15

;
-- Update records with the correct categoty combo
UPDATE datavalue
SET categoryoptioncomboid = 15
WHERE categoryoptioncomboid = 1714593
;


--###### Data value audits
with data1714593
AS(
	SELECT *
	from datavalueaudit
	where attributeoptioncomboid = 1714593

),
data15
AS(
	SELECT *
	from datavalueaudit
	where attributeoptioncomboid = 15

)
,
combine
AS(
SELECT d1.dataelementid
	, de.name as dataelement_name
	, d1.periodid
	, startdate
	, enddate
	, d1.organisationunitid
	, ou.name as ou_name
	, d1.categoryoptioncomboid
	, d1.value as value_1714593
	, d2.value as value_15
--INTO zz_datavalue_duplicates_defaults
from data1714593 d1
 join data15 d2 on d1.dataelementid = d2.dataelementid
				AND d1.periodid = d2.periodid
				AND d1.organisationunitid = d2.organisationunitid
				AND d1.categoryoptioncomboid = d2.categoryoptioncomboid
				--AND attributeoptioncomboid
JOIn dataelement de on de.dataelementid = d1.dataelementid
JOIN organisationunit ou on ou.organisationunitid = d1.organisationunitid
jOIN period  pe on pe.periodid = d1.periodid
--WHERE  d1.value<> d2.value
--and d1.lastupdated < d2.lastupdated
)

DELETE
FROM datavalueaudit d1
USING combine d2
WHERE d1.dataelementid = d2.dataelementid
	AND d1.periodid = d2.periodid
	AND d1.organisationunitid = d2.organisationunitid
	AND d1.categoryoptioncomboid = d2.categoryoptioncomboid
	AND attributeoptioncomboid = 15

;
-- Update records with the correct attribute combo
UPDATE datavalueaudit
SET attributeoptioncomboid = 15
WHERE attributeoptioncomboid = 1714593
;

---- data with different categoryoptioncombo
with data1714593
AS(
	SELECT *
	from datavalueaudit
	where categoryoptioncomboid = 1714593

),
data15
AS(
	SELECT *
	from datavalueaudit
	where categoryoptioncomboid = 15

)
,
combine 
AS(
	SELECT d1.dataelementid, d1.periodid, d1.organisationunitid, d1.attributeoptioncomboid, d1.value, d2.value
	from data1714593 d1
	join data15 d2 on d1.dataelementid = d2.dataelementid
					AND d1.periodid = d2.periodid
					AND d1.organisationunitid = d2.organisationunitid
					AND d1.attributeoptioncomboid = d2.attributeoptioncomboid
					--AND attributeoptioncomboid
	--WHERE d1.value<> d2.value
)

DELETE
FROM datavalueaudit d1
USING combine d2
WHERE d1.dataelementid = d2.dataelementid
	AND d1.periodid = d2.periodid
	AND d1.organisationunitid = d2.organisationunitid
	AND d1.attributeoptioncomboid = d2.attributeoptioncomboid
	AND categoryoptioncomboid = 15

;
UPDATE datavalueaudit
SET categoryoptioncomboid = 15
WHERE categoryoptioncomboid = 1714593
;


--=========  dataest completness registration
with data1714593
AS(
	SELECT *
	from completedatasetregistration
	where attributeoptioncomboid = 1714593

),
data15
AS(
	SELECT *
	from completedatasetregistration
	where attributeoptioncomboid = 15

)
,
combine
AS(
	SELECT d1.datasetid, d1.periodid, d1.sourceid
		from data1714593 d1
		join data15 d2 on d1.datasetid = d2.datasetid
						AND d1.periodid = d2.periodid
						AND d1.sourceid = d2.sourceid
						--AND d1.attributeoptioncomboid = d2.attributeoptioncomboid
)

DELETE
FROM completedatasetregistration d1
USING combine d2
WHERE d1.datasetid = d2.datasetid
	AND d1.periodid = d2.periodid
	AND d1.sourceid = d2.sourceid
	AND attributeoptioncomboid = 15
;

UPDATE completedatasetregistration
SET attributeoptioncomboid = 15
WHERE attributeoptioncomboid = 1714593
;

/*
Select *
from categorycombos_categories
WHERE categoryoptioncomboid in (15, 1714593)
*/
DELETE
FROM categoryoptioncombos_categoryoptions
WHERE categoryoptioncomboid = 1714593
;

DELETE
FROM categorycombos_optioncombos
WHERE categoryoptioncomboid  = 1714593
;


DELETE
from categoryoptioncombo
where categoryoptioncomboid  = 1714593