How do I create a SQL query to download data element form names, short names, descriptions and UIDs per programme

I’d like to import some new translations to our site (v2.30). To do this, I know I need to create a SQL query which gives me all the following:

  • DE short name
  • DE form name
  • DE description

per programme.

Also, if possible:
Programme stage names
Programme stage descriptions
Section names/descriptions
Category and Option names
Tracked entity attributes.

Would someone be able to help me out with the SQL query code, or point me to some guidance on how to do this (I’m not a developer so don’t really understand SQL coding!)?

Thanks!
Anna

2 Likes

Hope this helps:

select 
pg.name as program_name, 
de.shortname as dataelement_shortname,
de.name as dataelement_name, 
de.formname as dataelement_formname, 
de.description as dataelement_description,
pgs.name as stage_name, 
pgs.description as stage_description, 
pgss.name as section_name, 
pgss.description as section_description 
from programstagedataelement as pgsde inner join programstage as pgs on pgsde.programstageid=pgs.programstageid
inner join programstagesection as pgss on pgsde.programstageid=pgss.programstageid 
inner join dataelement as de on pgsde.dataelementid=de.dataelementid 
inner join program as pg on pgs.programid=pg.programid 
order by pg.name asc;

SQL View

2 Likes

This is amazing- thank you so much for such a quick response!

Would i have to do a separate SQL query for Categories and Options and for tracked entity attributes?

Anna

1 Like

And also how would I add in the UID for each element?

1 Like

For tracked entity attributes, you’ll do it separately. But for categories and category options you won’t need them as tracker dataelements don’t have category combination. It is default for both.

1 Like

for the uid

add de.uid as dataelement_uid on the select query

2 Likes

I’ve added the UID as you recommended so it now looks as I have pasted below.

However for some reason this doesn’t add the UID to the table when i run the query. Have I missed something out?

SELECT
pg.name as program_name,
de.shortname as dataelement_shortname,
de.name as dataelement_name,
de.formname as dataelement_formname,
de.description as dataelement_description,
de.uid as dataelement_uid,
pgs.name as stage_name,
pgs.description as stage_description,
pgss.name as section_name,
pgss.description as section_description
from programstagedataelement as pgsde INNER JOIN programstage as pgs ON pgsde.programstageid=pgs.programstageid
INNER JOIN programstagesection as pgss ON pgsde.programstageid=pgss.programstageid
INNER JOIN dataelement as de ON pgsde.dataelementid=de.dataelementid
INNER JOIN program as pg ON pgs.programid=pg.programid
order by pg.name asc;

1 Like

The query looks good. This might be an issue of caching, try creating a new sql view (no caching) and see the output.

2 Likes

Creating a new SQL view worked- thank you!

1 Like