Team,
Thank Jason and Murod i think this will help me a great deal. Jason you got
me me right. Murad your querry is running ok too.
This thread has been very helpful.
Cheers
On Fri, Nov 29, 2013 at 9:13 PM, Murod Latifov wrote:
> Hi Felix,
>
> Your sql query should be something like this:
>
Hi Felix,
Your sql query should be something like this:
select de.name as dename, ou.name as ouname, pe.startdate,
dv.dataelementid, categoryoptioncomboid, pe.periodid, value as val,
dv.sourceid as orgunitid from datavalue dv
left join dataelement de
on dv.dataelementid = de.dataelementid
left jo
Hi Jason,
Than as I said earlier, it should be fairly simple SQL query to datavalue
table, adding iner tables from which he wants DE name, Period name, etc. I
don't see any problem here. Call datavalue table, add data element name
from iner join of dataelement table and so forth for others, filter
Murod,
The issue here is that these values (text, etc) are not visible through
data set reports, as they are not able to be aggregated. So Felix needs a
way to get the "raw data" out, without any aggregation. Perhaps I am as
well totally off-topic as well, but this was the original request as I
und
Hi Felix,
It is still unclear to me. If you have a combination of yes/no, custom text
values you definitely need an advanced query pointing each criteria type or
create indicator that brings this all in one possible value. If you want
simply report these exact data just read them and print, should
Hi Felix,
Glad you have managed to clarify what this was all about. The most basic
query would be something like this
SELECT de.name as dataelement, p.startdate as period, ou.name as orgunit,
cc.categoryoptioncomboname as categoryoptioncomboname , dv.value as value
FROM datavalue dv
INNER JOIN d
Murod,
Ok, let me explain myself...
Assume i am collecting data with regard to facility Assessment and my data
elements
1) "Lighting and ventilation is observed to be adequate" (YES/NO)
2) "General state of facility" (Option Set) --i) Poor ii) Fair iii) Average
3) "Additional notes related to fac
Having orgunit level persisted in organisationunit table you would have
avoided lots of unnecessary inner outer joins.
If you want date from your query just try max(datefield), no type casting
needed.
On Fri, Nov 29, 2013 at 9:33 PM, Felix Obareh wrote:
> Team,
>
> Here is the query used by the
Hi Felix,
I do not understand for large on what you mean with "raw data" but can say
if you are not looking for aggregated data for the sake of SQL
requirements, just use count(1 as nothing) or count(value as value)
avoiding type cast issues.
best,
murod
On Fri, Nov 29, 2013 at 9:19 PM, Felix O
Team,
Here is the query used by the demo to generate the Immunization SQL based
report
select district.name as district, chiefdom.name as chiefdom, ou.name as
facility,
bcg.value as bcg, yellowfever.value as yellowfever, measles.value as
measles
from organisationunit ou
left outer join _orgunits
Thank you people for you input..
If i can clarify that the query is to pick raw data that otherwise is
recorded but we cannot retrieve because its not aggregated. This data would
include the following
Here is my full query:-
select county.name as county,subcounty.name as subcounty,division.name a
Hi Jason,
No need for the new thread. We do not have such data stored and we do not
use smartphones for unclean data collection. The regexp I presented is just
a hint on how to deal with such abnormal cases. You can tune it to what you
need, thats why regexp are there:)
What I initially said was w
Hi Murod,
Just trying to keep it focused on the original topic, but it seems you want
to persist. The initial part of this post was about getting out text data
(read the title). In this case, there is no need for any casting or
aggregation. Period.
That regex you post again unfortunately will a
Hi Jason,
I thought my points were of some help to solve the issue and discuss those
appearing from initial issue. What you thought about first call is not
right and what I proposed was a solution. After you added dummy data being
stored, where new topic was introduced (off initial topic). And now
Hi Murod,
I do not think anyone is happy about it, and thus my blueprint to provide
server-side regex validation of data, which has yet to be implemented.
However, the fact of the matter is, it can happen now, and having some data
is usually better than having no data at all. That regex which you
Hi Jason,
By telling these you mean data stored in DHIS2 at some implementations is
not trusted, that is not cleaned enough. How can one use these data for
analysis? Does it bring TRUE result for decision making? I don't think such
data should exist. At least some Data management action could clea
Hi Murod,
You would think this would work, and it might for you. The problem is there
is no way to "trust" that the raw data values can be cast. We have had a
lot of problems with this, but mostly because of 1) Legacy data before the
different value types were implemented and 2) data which is recei
Hi Jason,
Yes, it holds true in case if raw data being called. But query was with
aggregation elements. Technique I proposed will exactly remove none
convertible values from the query, thus there won't be cast exception. No
need to deploy heavy regex or stored procedure here, this is doable. Sampl
Should be fixed in 2.13 and trunk now.
Lars
On Wed, Nov 27, 2013 at 9:13 AM, Morten Olav Hansen wrote:
> The issue is this:
> Caused by: org.postgresql.util.PSQLException: ERROR: column
> degs.lv8uxn17zom does not exist
> Position: 401
>
> I think we might have to wait for Lars, he knows the
Well, it is very hard to see what is going on without the entire query. My
point here is there is no way to aggregate text, without some sort of
procedure, so you cannot cast anything, nor can you sum it, because in the
datavalue table, everything is stored as text. Just like with the
analytics, yo
I am afraid Jason, it won't work that way. In Postgres aggregating (SUM,
AVG) for varchar will give exception. There is no onbuilt type casting at
this point.
On Fri, Nov 29, 2013 at 5:09 PM, Jason Pickering <
jason.p.picker...@gmail.com> wrote:
> You cannot cast text to a double so just remove
You cannot cast text to a double so just remove that cast operator and you
should be fine.
--Sent from my mobile
On Nov 29, 2013 1:16 PM, "Felix Obareh" wrote:
> Thank Jason,
> I have decided to go with the SQL Query. I am using the SQL Query example
> i the user manual together with the demo da
Hi Obare,
You also need to join dataelement table and add the followoing in your
WHERE clause:
domaintype='aggregate' and aggregationtype= bla bla to reduce in away that
non numeric values do not get into query. Hope this helps.
best,
murod
On Fri, Nov 29, 2013 at 4:16 PM, Felix Obareh wrote:
Thank Jason,
I have decided to go with the SQL Query. I am using the SQL Query example i
the user manual together with the demo database for Sierra Leone.
It is working fine for elements of integer type and i can run the query
from my pgAdmin and i get them fine. I have a problem with displaying d
Someone logged in and changed it.. I just rechanged it to "District123" for
now as the user interface does not allow "district". Will be reset to
"district" tomorrow morning.
On Fri, Nov 29, 2013 at 6:58 AM, Felix Obareh wrote:
> Hi,
>
> The useraname admin/district for the demo site is invalid.
25 matches
Mail list logo
