AQL queries and one-many relationships
Sorry, I was trying to use an example to explain that in SQL one would have a cartesian join if you have select t1.* from t1, t2 but in AQL the examples I have seen suggest that select o from c1, o1 would be an implict join I'll leave the AQL discussions to someone more versed with it :-) > -- > > Message: 4 > Date: Wed, 25 Feb 2009 09:08:28 +1100 > From: > Subject: RE: AQL queries and one-many relationships > To: > Message-ID: > ? ? ? ?<8C3F2174B3FE2B408CB380513186BEC45752819AE7 at > EXNSW-MBX03.nexus.csiro.au> > > Content-Type: text/plain; charset="iso-8859-1" > > Thanks for your respose Greg. > > I'm not really concerned about the details of specific archetypes - I just > used the ubiquitous blood pressure one because that's the one used in a lot > of the example documentation. > > My question is more about the how AQL should handle querying data that > conforms to archetypes that contain one or more one-to-many relationships. > > John >
AQL queries and one-many relationships
Hello. I am currently investigating AQL and would like to know how the following queries would be answered by a conformant AQL query engine. I understand that the structure of AQL query results are not standardized yet in the AQL specifications, so if the spec cannot give a definitive answer what would Ocean's AQL query engine implementation do? 1. SELECT o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value AS PosturalChangeSystolic, o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value AS ParadoxSystolic FROM EHR CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= 140 OR o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= 140 This query attempts to find all Systolic readings for Paradox and Postural Change blood pressure events where the Systolic reading for either is >= 140. As there is a one-to-many relationship between a blood pressure observation and both Paradox and Postural Change events, how should the query be processed? If an observation has three Postural Change events where Systolic >= 140, and four such Paradox events, would the query return twelve rows (using my relational database thinking). Or would one row be returned, having two "lists" (with three and four members respectively) of Systolic readings? 2. SELECT o FROM EHR CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= 140 OR o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= 140 This query is the same as the previous one, except that it returns the whole observation. It seems to me that all readings should be returned, regardless of their systolic values? 3. SELECT o FROM EHR CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] WHERE o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= 140 AND o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= 140 This query is the same as the previous one, except that the OR has been changed to an AND. It could be argued that only the Paradox and Postural Change events with a Systolic reading >= 140 should be returned, but it could also be argued that all readings should be returned, as the whole observation has been selected. The problem I have is how to treat queries which have in the WHERE clause a path expression that traverses through a 1:n relationship. In trying to think through the semantics of such queries, I come up with ambiguities. In a relational query (i.e. SQL) the "equivalent" path expression would have to be expressed as a join between tables in the FROM clause, thus removing the ambiguities. Am I missing something or are my concerns relevant? If so, how does the spec/Ocean implementation address them? Thanks, John Ryan-Brown The Australian e-Health Research Centre CSIRO ICT Centre Brisbane
AW: OpenEHR'ED
An HTML attachment was scrubbed... URL: <http://lists.openehr.org/mailman/private/openehr-technical_lists.openehr.org/attachments/20090224/e06f8446/attachment.html> -- next part -- A non-text attachment was scrubbed... Name: dmeyer.vcf Type: application/octet-stream Size: 158 bytes Desc: not available URL: <http://lists.openehr.org/mailman/private/openehr-technical_lists.openehr.org/attachments/20090224/e06f8446/attachment.vcf>
AW: OpenEHR'ED
Stefan, I haven't defined a format yet, but my experience is that power point on its own is too sketchy. It will either be necessary to have lecturers' notes to accompany the PPT, or to have a workbook of exercises. My preference would be for the workbook. - Derek, - Original Message - From: Stefan Sauermann Date: Tuesday, February 24, 2009 1:40 pm Subject: AW: OpenEHR'ED To: 'For openEHR clinical discussions' , openehr-technical at openehr.org Cc: 'Georg Duftschmid' , 'Wolfgang Dorda' > > Hello! > I guess we might be interested to be part of such a thing. We do > have a > research project starting right now, which should also tackle > archetypetechnology. We are supposed to come up with a report, and > teaching material > may come in handy. > > There are already some seminar papers from students about > archetypes, will > see if this is available. I myself have taught Archetypes a number > of times, > and always used Tom Beale's paper as a starter, and a few HL7 RIM > slides. > > What areas of content are you intending to provide? > In which way is this going to be packed? powerpoint? Book chapters? > > Stefan Sauermann > > > > > -Urspr?ngliche Nachricht- > > Von: openehr-clinical-bounces at openehr.org > > [mailto:openehr-clinical-bounces at openehr.org] Im Auftrag von > > Derek Meyer > > Gesendet: Dienstag, 24. Februar 2009 11:39 > > An: openehr-technical at openehr.org > > Cc: openehr-clinical at openehr.org > > Betreff: OpenEHR'ED > > > > Hi, > > > > I'm thinking about teaching some aspects of openEHR to our > > BSc Biomedical Informatics undergraduates. > > > > In the spirit of open Source I was thinking of doing this by > > developing open Courseware - or enhancing existing course > > ware, if any exists. > > > > Does anyone know if any course ware on openEHR exists, and if > > not, would be interested in developing some? > > > > BWs > > > > Derek > > > > > ___ > openEHR-clinical mailing list > openEHR-clinical at openehr.org > http://lists.chime.ucl.ac.uk/mailman/listinfo/openehr-clinical > -- next part -- A non-text attachment was scrubbed... Name: dmeyer.vcf Type: text/x-vcard Size: 150 bytes Desc: Card for Derek Meyer URL: <http://lists.openehr.org/mailman/private/openehr-technical_lists.openehr.org/attachments/20090224/8a140311/attachment.vcf>
AQL queries and one-many relationships
My assumption was that the 'contains openEHR-EHR-COMPOSITION.encounter.v1' will restrict the result set to be in this case blood pressure observations that were recorded on an encounter note (or any document identified as such). If I make up an archetype called assessment you could have the following to get blood pressure results from two different document types - not sure if they intention was for all assessments to be designated as encounter notes though - perhaps. SELECT o FROM EHR CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] AND CONTAINS COMPOSITION a [openEHR-EHR-COMPOSITION.assessment.v1] CONTAINS OBSERVATION o2 [openEHR-EHR-OBSERVATION.blood_pressure.v1] Greg http://www.patientos.org On Tue, Feb 24, 2009 at 7:00 AM, wrote: > Send openEHR-technical mailing list submissions to > ? ? ? ?openehr-technical at openehr.org > > To subscribe or unsubscribe via the World Wide Web, visit > ? ? ? ?http://lists.chime.ucl.ac.uk/mailman/listinfo/openehr-technical > or, via email, send a message with subject or body 'help' to > ? ? ? ?openehr-technical-request at openehr.org > > You can reach the person managing the list at > ? ? ? ?openehr-technical-owner at openehr.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of openEHR-technical digest..." > > > Today's Topics: > > ? 1. AQL queries and one-many relationships (John.Ryan-Brown at csiro.au) > ? 2. OpenEHR'ED (Derek Meyer) > > > -- > > Message: 1 > Date: Tue, 24 Feb 2009 17:23:00 +1100 > From: > Subject: AQL queries and one-many relationships > To: > Message-ID: > ? ? ? ?<8C3F2174B3FE2B408CB380513186BEC45752819AE6 at > EXNSW-MBX03.nexus.csiro.au> > > Content-Type: text/plain; charset="us-ascii" > > Hello. I am currently investigating AQL and would like to know how the > following queries would be answered by a conformant AQL query engine. I > understand that the structure of AQL query results are not standardized yet > in the AQL specifications, so if the spec cannot give a definitive answer > what would Ocean's AQL query engine implementation do? > > 1. ?SELECT > ? ? ?o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value AS > PosturalChangeSystolic, > ? ? ?o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value AS > ParadoxSystolic > ? ?FROM > ? ? ?EHR > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > ? ? ?CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] > ? ?WHERE > ? ? ?o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= > 140 OR > ? ? ?o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= > 140 > > This query attempts to find all Systolic readings for Paradox and Postural > Change blood pressure events where the Systolic reading for either is >= 140. > As there is a one-to-many relationship between a blood pressure observation > and both Paradox and Postural Change events, how should the query be > processed? If an observation has three Postural Change events where Systolic > >= 140, and four such Paradox events, would the query return twelve rows > (using my relational database thinking). Or would one row be returned, having > two "lists" (with three and four members respectively) of Systolic readings? > > > 2. ?SELECT o > ? ?FROM > ? ? ?EHR > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > ? ? ?CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] > ? ?WHERE > ? ? ?o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= > 140 OR > ? ? ?o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= > 140 > > This query is the same as the previous one, except that it returns the whole > observation. It seems to me that all readings should be returned, regardless > of their systolic values? > > > 3. ?SELECT o > ? ?FROM > ? ? ?EHR > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > ? ? ?CONTAINS OBSERVATION o [openEHR-EHR-OBSERVATION.blood_pressure.v1] > ? ?WHERE > ? ? ?o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value/value >= > 140 AND > ? ? ?o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value/value >= > 140 > > This query is the same as the previous one, except that the OR has been > changed to an AND. It could be argued that only the Paradox and Postural > Change events with a Systolic reading >= 140 should be returned, but it could > also be argued that all readings should be returned, as the whole observation > has been selected. > > > The problem I have is how to treat queries which have in the WHERE clause a > path expression that traverses through a 1:n relationship. In trying to think > through the semantics of such queries, I come up with ambiguities. In a > relational query (i.e. SQL) the "equivalent" path ex
OpenEHR'ED
Hi, I'm thinking about teaching some aspects of openEHR to our BSc Biomedical Informatics undergraduates. In the spirit of open Source I was thinking of doing this by developing open Courseware - or enhancing existing course ware, if any exists. Does anyone know if any course ware on openEHR exists, and if not, would be interested in developing some? BWs Derek -- next part -- A non-text attachment was scrubbed... Name: dmeyer.vcf Type: text/x-vcard Size: 150 bytes Desc: Card for Derek Meyer URL: <http://lists.openehr.org/mailman/private/openehr-technical_lists.openehr.org/attachments/20090224/ed73607b/attachment.vcf>
