Re: [Dhis2-users] SQL view question

[Hinds, Ms. Angela (CAR)]

Thank you very much everyone, for your guidance.  I don't have a copy of the 
database locally.  That actually is a good idea and I will do that.  If I have 
any problems, I'll email.

Regards,

Angela

Get Outlook for iOS




On Thu, Aug 25, 2016 at 8:13 AM -0400, "David Muturi" 
> wrote:

Hi Angela,
Its usually easier to write the queries using a copy of the database stored 
locally and using the database access tools described above, test them before 
copying them over to the SQL VIEW for front end access using dhis2, one is able 
to easily troubleshoot any errors in the scripts and fix than when using sql 
view directly.
rgds
David

2016-08-25 14:53 GMT+03:00 David Muturi 
>:
Hi Angela,
You will need to access the database, ideally having a local copy on your 
laptop and using GUI tools i.e. Pgadmin, Navicat for PostgresSQl or other tools 
will allow you to access the backend. From there the names for the tables can 
serve as a guide e.g. datalement for dataelements, dataset for datasets, e.t.c 
For data, DHIS2 uses the "long and skinny" or EAV model so the 
dataelement,Source(orgunit),category (i.e. meta data) are stored together with  
the data in the datavalues table.
In case of any question regarding writing sql queries please free to get in 
touch with me.
rgds
David

2016-08-25 14:21 GMT+03:00 Hinds, Ms. Angela (CAR) 
>:
Hi

I have not used the SQL view before and after perusing the documentation, I am 
still not clear on

1.   Which name references are to be used in the SQL statement for the 
elements and table?  My current element names and short names used by the forms 
have spaces

2.   Using the web.api how do I get a list of the relevant reference names 
to use for the particular dataset

Regards,

Thank you.



___
Mailing list: https://launchpad.net/~dhis2-users
Post to : 
dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp



___
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp

Re: [Dhis2-users] SQL view question

[David Muturi]

Hi Angela,
Its usually easier to write the queries using a copy of the database stored
locally and using the database access tools described above, test them
before copying them over to the SQL VIEW for front end access using dhis2,
one is able to easily troubleshoot any errors in the scripts and fix than
when using sql view directly.
rgds
David

2016-08-25 14:53 GMT+03:00 David Muturi :

> Hi Angela,
> You will need to access the database, ideally having a local copy on your
> laptop and using GUI tools i.e. Pgadmin, Navicat for PostgresSQl or other
> tools will allow you to access the backend. From there the names for the
> tables can serve as a guide e.g. datalement for dataelements, dataset for
> datasets, e.t.c For data, DHIS2 uses the "long and skinny" or EAV model so
> the dataelement,Source(orgunit),category (i.e. meta data) are stored
> together with  the data in the datavalues table.
> In case of any question regarding writing sql queries please free to get
> in touch with me.
> rgds
> David
>
> 2016-08-25 14:21 GMT+03:00 Hinds, Ms. Angela (CAR) :
>
>> Hi
>>
>>
>>
>> I have not used the SQL view before and after perusing the documentation,
>> I am still not clear on
>>
>> 1.   Which name references are to be used in the SQL statement for
>> the elements and table?  My current element names and short names used by
>> the forms have spaces
>>
>> 2.   Using the web.api how do I get a list of the relevant reference
>> names to use for the particular dataset
>>
>>
>>
>> Regards,
>>
>>
>>
>> Thank you.
>>
>>
>>
>>
>>
>> ___
>> Mailing list: https://launchpad.net/~dhis2-users
>> Post to : dhis2-users@lists.launchpad.net
>> Unsubscribe : https://launchpad.net/~dhis2-users
>> More help   : https://help.launchpad.net/ListHelp
>>
>>
>
___
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp

Re: [Dhis2-users] SQL view question

[David Muturi]

Hi Angela,
You will need to access the database, ideally having a local copy on your
laptop and using GUI tools i.e. Pgadmin, Navicat for PostgresSQl or other
tools will allow you to access the backend. From there the names for the
tables can serve as a guide e.g. datalement for dataelements, dataset for
datasets, e.t.c For data, DHIS2 uses the "long and skinny" or EAV model so
the dataelement,Source(orgunit),category (i.e. meta data) are stored
together with  the data in the datavalues table.
In case of any question regarding writing sql queries please free to get in
touch with me.
rgds
David

2016-08-25 14:21 GMT+03:00 Hinds, Ms. Angela (CAR) :

> Hi
>
>
>
> I have not used the SQL view before and after perusing the documentation,
> I am still not clear on
>
> 1.   Which name references are to be used in the SQL statement for
> the elements and table?  My current element names and short names used by
> the forms have spaces
>
> 2.   Using the web.api how do I get a list of the relevant reference
> names to use for the particular dataset
>
>
>
> Regards,
>
>
>
> Thank you.
>
>
>
>
>
> ___
> Mailing list: https://launchpad.net/~dhis2-users
> Post to : dhis2-users@lists.launchpad.net
> Unsubscribe : https://launchpad.net/~dhis2-users
> More help   : https://help.launchpad.net/ListHelp
>
>
___
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp

Re: [Dhis2-users] SQL view question

[Halvdan Hoem Grelland]

Hi,

1) The SQLView feature is basically just a convenient way to expose the
database directly, meaning you query the database tables with straight SQL
statements. Unfortunately there's no documentation detailing names of
tables and columns in the DB, so you'll have to look them up in an instance
of a DHIS2 db yourself. PgAdmin III or similar are good tools to do so (if
using postgres).

In any case, you'll find that most names are fairly self-explanatory. For
example: DataElement is found in the dataelement table, which has a
dataelementid, a uid, a name, a shortname and so forth.

2) In the web api we expose the "id" of datasets (or any object for that
matter), which is really the "uid" in the database. You can query the api
for the id, and then run queries against the uid column of any object using
these.

For example to fetch the name and uid of all dataSets you would do
https://play.dhis2.org/dev/api/dataSets.json?fields=name,id=false

On Thu, Aug 25, 2016 at 1:21 PM, Hinds, Ms. Angela (CAR) <
hinds...@carpha.org> wrote:

> Hi
>
>
>
> I have not used the SQL view before and after perusing the documentation,
> I am still not clear on
>
> 1.   Which name references are to be used in the SQL statement for
> the elements and table?  My current element names and short names used by
> the forms have spaces
>
> 2.   Using the web.api how do I get a list of the relevant reference
> names to use for the particular dataset
>
>
>
> Regards,
>
>
>
> Thank you.
>
>
>
>
>
> ___
> Mailing list: https://launchpad.net/~dhis2-users
> Post to : dhis2-users@lists.launchpad.net
> 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 
___
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp

Re: [Dhis2-users] SQL view question

[Hinds, Ms. Angela (CAR)]

Hi

I have not used the SQL view before and after perusing the documentation, I am 
still not clear on

1.   Which name references are to be used in the SQL statement for the 
elements and table?  My current element names and short names used by the forms 
have spaces

2.   Using the web.api how do I get a list of the relevant reference names 
to use for the particular dataset

Regards,

Thank you.


___
Mailing list: https://launchpad.net/~dhis2-users
Post to : dhis2-users@lists.launchpad.net
Unsubscribe : https://launchpad.net/~dhis2-users
More help   : https://help.launchpad.net/ListHelp