I’m working with SQL Cohort Queries in the Reporting Module. I notice that for any SQL in the SQL Cohort Query, the result is 1 Patients (for preview as well as run).
There isn’t any stacktrace being generated for preview or run.
I’m running…
Am I using it wrong or is it an error? I dont see any issue similar to this in JIRA. Any comments @mseaton or @mogoodrich ?
Generally SQL queries in the Reporting module work.
Can you show the example you’re trying? (You need to have a patient_id column come back from the query, if I remember right.)
I am trying to retrieve the count of patients. These screenshots are from demo.openmrs.org
When I do a all patients cohort the result is 30 patients.
This is the SQL cohort query that I am saving and running
This is the result for that SQL cohort query and this seems to be the only result for any query.
@maurya - the issue is that you are not querying for a cohort, you are querying for a number. If you wish to use Cohort Definitions to return a Cohort of patients (from which you can get the members themselves, or the count of members), then you need your query to return the patient_ids that match your needs.
So, the equivalent of the “AllPatientsQuery” as a SQL Cohort Definition would be configured as:
select patient_id from patient where voided = 0;
As @darius said in his earlier response, you need to be returning patient_id from your query in order to use SqlCohortDefinitions correctly.
If all you want is an indicator value based on Sql, you can use a SqlIndicator for this and bypass Cohorts entirely. This is where configuring the SQL as “select count(*) from patient where voided = 0” will work as you intend.
The difference being, that in the Cohort Definition context, doing “select count(*) from patient” will return the number 30, and this will be interpreted as a Cohort of size 1 containing only the patient whose patient_id = 30.
Hope this clarifies.
Mike
