No results apear in Pivot Table

Hi everyone…
i have a problem with updating my pivot talbes … all the results in 2023 didn’t apear …
I had tried to generate Analytics tables from Analytics tables management and the error apears:

Process failed: StatementCallback; uncategorized SQLException for SQL [drop table _categorystructure;]; SQL state [2BP01]; error code [0]; ERROR: cannot drop table _categorystructure because other objects depend on it Detail: view _view_tests depends on table _categorystructure Hint: Use DROP … CASCADE to drop the dependent objects too.;

I tried to generate the resource tables too … it gave me thwe same error…

the Versionn of my dhis2 is: 2.36.10.1-SNAPSHO

any advice or solutions …
thanks at all …

It looks like the SQL View “_view_tests” is making reference to the _categorystructure table. This table gets dropped and recreated during analytics, but since the view depends on it, it cannot be dropped and the process fails.

I suggest that you delete that view, if it really is a test.

Otherwise, if you really need it, and you really need to reference the _categorystructure table, you will need to create a function in your database and then call the function to return the view. This approach effectively masks the dependency of the _categorystructure table from the database, so it can be dropped.

Regards,
Jason

3 Likes

Hi @Jamal l In case you did not create the sql view named “view_tests” (perhaps someone else on the team) - it is located in the Maintenance app > Other > SQL views. It needs to be edited or removed so it is not referencing the _categorystructure table.

2 Likes