How to query for compositions where fields are not null

Specifications AQL
1

Context:
In SQL I frequently use the “IS NOT NULL” operator, to constrain the search results of objects, where the given field is not null and thus not empty.

I am currently trying the same thing with AQL, where I want to query for compositions which have the field “end_time” set and does not include any results, where “end_time” is empty and thus null - or undefined depending on the implementation.

Question:
How do we query in AQL where the WHERE clause includes the constraint, where “end_time” is not null, such as in the following example that does not run - but not because I query for either of these two compositions :wink: :

SELECT c1, c2
FROM
COMPOSITION c1 [openEHR-EHR-COMPOSITION.prescription.v0]
OR
COMPOSITION c2 [openEHR-EHR-COMPOSITION.prescription-cistec.v0]
WHERE
c1/context/end_time IS NOT NULL
OR
c2/context/end_time IS NOT NULL

2

The good news: If I understand you correctly, the EXISTS operator should be the right one:

The bad news: it’s not yet implemented in EHRbase. But my sense is that many users are quite eager on this, so we need to assess when this can be prioritized.

6 Likes
3

Thanks a lot. I have read the AQL specification and I assumed, that the EXISTS operator only applies to archeTYPES and not individual fields.

Me personally, I would appreciate, if EHRBase could support this operator because our current EHR source code deploys the “IS NOT NULL” operation in SQL quite frequently and it is a very helpful operator to constrain search results, if no additional constraints are required - because we are too lazy :wink: .

For the implementation guides at our company, I will consequently document only AQL queries, where the EXISTS operator is not used, such that it may run on ?any? platform.

Thanks to your answer, the AQL query works now and you saved me a lot of time!

The solution would be

SELECT c1, c2
FROM
    COMPOSITION c1 [openEHR-EHR-COMPOSITION.prescription.v0]
    OR
    COMPOSITION c2 [openEHR-EHR-COMPOSITION.prescription-cistec.v0]
WHERE
    EXISTS c1/context/end_time 
    OR
    EXISTS c2/context/end_time
1 Like
4

Really need this …Exists needs to exist if you see what I mean…

1 Like
5

I have struggled with trying to get results with EXISTs or NOT EXIST
In the end I resort to work around of filtering by value:
such as
WHERE c1/context/end_time /value >= '1990-01-01' OR c2/context/end_time /value >= '1990-01-01'

1 Like
6

Hi Michael

Thank you for posting the workaround. As far as I know the operator is spelled EXISTS and it does not work if the “s” at the end is left out. Furthermore, I apply this operator on Better’s CDR. From Birger’s post in this thread, we now know, that EXISTS does not exist in EHRBase and thus only applies in Better’s CDR.

If you are on Better’s CDR try to spell EXISTS with an “S” at the end. As it is not case-sensitive “EXISTs” works, too.

I will include your workaround in the implementation guide for the developers. Thanks for that!