← Back to team overview

dhis2-users team mailing list archive

Re: [Dhis2-devs] DHIS2 SQL View - practical examples

 

Very briefly, the core tables for aggregate data include datavalue,
organisationunit, period, and dataelement.

Tables are linked on ids, e.g. organisatoinunitid. There are also some very
helpful tables starting with underscore _.

Knut

On Wed, Jul 6, 2016 at 12:14 PM, Halvdan Hoem Grelland <halvdan@xxxxxxxxx>
wrote:

> We do not have extensive documentation directly for the DB schema. You
> will have to browse the schema of an instance using a tool of your choosing
> (pgAdmin, psql). For metadata the source Java beans and Hibernate mappings
> (*.hbm.xml) might also be useful to understand the relations (especially
> for meta data). It's all pretty standard stuff, though, so it's not hard to
> figure out for simple use cases like the one you mentioned (see Jason's
> example query).
>
> On Wed, Jul 6, 2016 at 11:04 AM, channara rin <rin.channara@xxxxxxxxx>
> wrote:
>
>> Hi all,
>> do you have some explaination relate to table schema?
>> when i open, it alot of table.
>>
>> thank you
>> channara
>>
>> On Tue, Jul 5, 2016 at 8:19 PM, Raminosoa Rabemanantsoa, Tantely <
>> traminosoa@xxxxxxxxxx> wrote:
>>
>>> Thank you Michael.
>>>
>>> Regards,
>>>
>>> Tantely.
>>>
>>> On Tue, Jul 5, 2016 at 3:35 PM, Michael Mwebaze <
>>> michael.mwebaze@xxxxxxxxx> wrote:
>>>
>>>> Hi Tantely,
>>>>
>>>> Standard ANSI SQL can be executed easily with-in Sqlviews sub-module in
>>>> DHIS2. The link below should be helpful.
>>>>
>>>> http://dhis2.github.io/dhis2-docs/2.22/en/user/html/ch22s06.html
>>>>
>>>> Regards,
>>>>
>>>> Michael
>>>>
>>>> On 5 July 2016 at 04:45, Raminosoa Rabemanantsoa, Tantely <
>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>
>>>>> Thank you Knut for your suggestion.
>>>>>
>>>>> Regards,
>>>>>
>>>>> Tantely.
>>>>>
>>>>> On Tue, Jul 5, 2016 at 10:53 AM, Knut Staring <knutst@xxxxxxxxx>
>>>>> wrote:
>>>>>
>>>>>> Hi Raminosa,
>>>>>>
>>>>>> I think you should try to install Postgres locally on your laptop,
>>>>>> which would allow you to easily query the database using PgAdmin.
>>>>>>
>>>>>> Regards,
>>>>>> Knut
>>>>>>
>>>>>> On Tue, Jul 5, 2016 at 9:13 AM, Raminosoa Rabemanantsoa, Tantely <
>>>>>> traminosoa@xxxxxxxxxx> wrote:
>>>>>>
>>>>>>> Hi All,
>>>>>>>
>>>>>>>
>>>>>>> I am looking for a documentation of the use of the SQL syntax to run
>>>>>>> in SQL View  (table names, field names specific to DHIS2 ). I have seen for
>>>>>>> example to query organization unit, we have to select from “ou” or to use
>>>>>>> “dv” to select the datavalues. I am planning to list all the uid, code and
>>>>>>> names of all organization units at all levels (1 to 6) in one single query
>>>>>>> result.
>>>>>>>
>>>>>>>
>>>>>>> Thank you for your support.
>>>>>>>
>>>>>>>
>>>>>>> Tantely.
>>>>>>>
>>>>>>> *This message and its attachments are confidential and solely for
>>>>>>> the intended recipients. If received in error, please delete them and
>>>>>>> notify the sender via reply e-mail immediately.*
>>>>>>> _______________________________________________
>>>>>>> Mailing list: https://launchpad.net/~dhis2-devs
>>>>>>> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
>>>>>>> Unsubscribe : https://launchpad.net/~dhis2-devs
>>>>>>> More help   : https://help.launchpad.net/ListHelp
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Knut Staring
>>>>>> Dept. of Informatics, University of Oslo
>>>>>> Norway: +4791880522
>>>>>> Skype: knutstar
>>>>>> http://dhis2.org
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>> *This message and its attachments are confidential and solely for the
>>> intended recipients. If received in error, please delete them and notify
>>> the sender via reply e-mail immediately.*
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-users
>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>> More help   : https://help.launchpad.net/ListHelp
>>>
>>>
>>
>> _______________________________________________
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
>
> --
> Halvdan Hoem Grelland
> Software developer, DHIS 2
> University of Oslo
> http://www.dhis2.org <https://www.dhis2.org/>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>


-- 
Knut Staring
Dept. of Informatics, University of Oslo
Norway: +4791880522
Skype: knutstar
http://dhis2.org

References