Thanks.
What changed was probably the SQLCipher version for Android. Changed to the
64 bits version. It definitely worked fine before.
Will try your first query.
RBS
On Sun, 22 Sep 2019, 18:37 Keith Medcalf, <kmedc...@dessus.com> wrote:
>
> On Sunday, 22 September, 2019 04:10, Bart Smissaert <
> bart.smissa...@gmail.com> wrote:
>
> >Have this query:
>
> >SELECT round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) AS AGE,
> > AVG(BM.numeric_value) AS avg_Hb_Male,
> > AVG(BF.numeric_value) as avg_Hb_Female
> >FROM PATIENTS P INNER JOIN NUM_VALUES BM ON(P.ID = BM.ID AND P.SEX =
> 'Male')
> > INNER join NUM_VALUES BF ON(P.ID = BF.ID AND P.SEX =
> 'Female')
> >WHERE BF.term_text = 'Haemoglobin estimation' and
> > BM.term_text = 'Haemoglobin estimation'
> >GROUP BY AGE
> >HAVING AGE > 18 and AGE < 98
> >ORDER BY AGE ASC
>
> >Which always used to run fine and return rows. There is no error, but
> >there are no returned rows.
>
> What changed between when it was "working" and when it was "not working"?
> As far as I can see it should never have been "working" because the eqijoin
> constraints could never be satisfied except by a Patients that was both
> Male and Female at the same time in the same record (which is impossible).
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg(case when P.Sex == 'Male' then V.numeric_value else null end)
> as avg_Hb_Male,
> avg(case when P.Sex == 'Female' then V.numeric_value else null
> end) as avg_Hb_Female
> from Patients P, num_values V
> where P.id == V.id
> and V.term_text == 'Haemoglobin estimation'
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> or, using outer joins:
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg(BM.numeric_value) as avg_Hb_Male,
> avg(BF.numeric_value) as avg_Hb_Female
> from Patients as P
> left join num_values as BM
> on P.id == BM.id
> and P.Sex == 'Male'
> and BM.term_text == 'Haemoglobin estimation'
> left join num_values as BF
> on P.id == BF.id
> and P.Sex == 'Female'
> and BF.term_text == 'Haemoglobin estimation'
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> or, using correlated subqueries:
>
> select round(((Julianday('now') - 2415018.5) - P.DOB) / 365.25, 0) as
> Age,
> avg((
> select numeric_value
> from num_values as V
> where P.id == V.id
> and P.Sex == 'Male'
> and V.term_text == 'Haemoglobin estimation'
> )) as avg_Hb_Male,
> avg((
> select numeric_value
> from num_values as V
> where P.id == V.id
> and P.Sex == 'Female'
> and V.term_text == 'Haemoglobin estimation'
> )) as avg_Hb_Female
> from Patients as P
> group by Age
> having Age between 19 and 97
> order by Age asc;
>
> The first one is likely to be the fastest to execute since it does the
> least record lookups and the optimizer has the best chance at optimizing
> the query.
>
>
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
