Hello team,
I would like to know how to create a materialized view from event reports.
Or how do I query data for specific program data elements in SQL?
Mohammed
cc: @Gassim
Thank you for your question! I’m wondering why materialized view vs the standard SQL View? I think you might find this helpful: Visualizations - DHIS2 Documentation
First, I’d start with getting the list of tables:
SELECT * FROM pg_tables
WHERE schemaname = 'public';
You can see the name of tables that are related to your query:
programstage
dataelement
programstagedataelement
programstagesection_dataelements
Then you will need to know which columns you want from those tables and create a SQL query.
Another way to go about this is to use the API. For example, domainInstance/api/programs/programUID?fields=name,programStages[programStageDataElements[programStage[name],dataElement[name,valueType]]]