Behaviour of logical operators(OR, AND, NOT) in FROM Clause

Implementation Integration
16

you mean
WHERE NOT EXISTS comp/ right? :stuck_out_tongue:

2 Likes
17

[moderator: query formatted for readability]

Hi, i am also trying to get ehrids from two templates, where the data points in the two templates matches some criteria. But getting empty rows if i use AND operator.

Aql used :

{
    "q" : "
SELECT 
    e/ehr_id/value as ehrIds 
FROM 
    ehr e CONTAINS 
        COMPOSITION c[openEHR-EHR-COMPOSITION.encounter.v1]
        CONTAINS (EVALUATION e1[openEHR-EHR-EVALUATION.gender.v1]) 
WHERE
    c/archetype_details/template_id/value='temp1' AND 
    c/archetype_details/template_id/value='temp2' AND 
    e1/data[at0002]/items[at0022]/value/mappings/target/code_string='snomed_code' AND 
    c/items[at0001]/value/defining_code/code_string='snomed_code' 
    ORDER BY c/context/start_time/value DESC"
}

Can anyone please help me out on how to use AND operator if we are querying on two templates.
Thanks in advance.

18

I guess if you need to search for two templates, then you need to use ‘OR’, not ‘AND’:

...
WHERE (c/archetype_details/template_id/value=‘patient_personal_data.en.v1’ OR c/archetype_details/template_id/value=‘screening_oral.en.v0’)
   AND ...

In theory you could also use AQL matches operator.

19

First: please consider formatting your aql queries when posting questions. It makes it easy for others to help you.

Second: as @sebastian.iancu says, you need to use OR, not AND . Think about it:
each composition has a single value for its ../template_id/value path. You’re asking the AQL engine to get you compositions which have value x and value y at this path. Since there is only one value set at that path , there are no matching compositions. There can never be.

1 Like
20

If i use OR, then i will get the ehrId’s if any one of the criteria matches. But I want the ehrId’s where it matches the both scenarios.

21

Matches does makes the query a little simpler (but does the same thing)

WHERE c/archetype_details/template_id/value MATCHES { 'screening_oral.en.v0’, 'patient_personal_data.en.v1'} ```
22

MATCHES also doing the same. Its performing an OR Operator. getting the wrong results.

23

First: you’re using a path in the form of e1/data[at002]... Is that supposed to be c/data... ? I don’t think there’s such a path under EHR in RM

Assuming that’s supposed to be c/.., you need to confirm templates with both template ids have the same structure so that compositions with either template id can in theory match the criteria for snomed code.

Assuming that is also the case, you may want to use parenthesis to ensure operator precedence, as in pseudo code:

(template ID = foo OR template ID = bar) AND (c/../ = snomed code AND c/../ = snomed code)
24

I used the same composition archetype in the two templates. So the moment i gave AND, it’s giving empty rows.

25

Try that - as @Seref said, you are missing parentheses around the template OR statement. THere is still something odd about the where clauses so I have stripped out some parts out and added some Selects to give a better understanding.

26

Don’t know about AQL, but in SQL the solution would be to use set functions like INTERSECT, and execute two queries, one for each template ID, then the result will be the intersection of both subqueries.

Just checked and the AQL spec doesn’t support set functions.

27

Yes, in the AQL also I did the same using matches keyword. And it worked. Thanks

28

Great - to help others, could you share the AQL that you managed to get working?

Ian

1 Like
29

We need to write two aqls and take the ehrids from 1st aql and pass them in the matches condition of second aql along with the filter criteria. Then we will get only common id’s which will satisfy both conditions

30

That’s a workaround for the missing set operators in AQL mentioned above. I guess the next version should consider UNION and INTERSECT operators.

31

I’m still not convinced that should be necessary. I would have expected a single query to have worked.

@Sravanthi_Kovi - which CDR product are you using?

1 Like
32

@ian.mcnicoll The OR in the WHERE for the template_id doesn’t assure the EHR contains COMPOS for both template_ids. I understood in the original query that was the requirement: retrieve EHRs which contains COMPOSITIONs for both template_ids (AT THE SAME TIME, not one or the other, both instances should exist) and have certain code at some coded text.

Correct me if I’m wrong @Sravanthi_Kovi

1 Like
33

Thx Pablo, That makes sense now. I hd not appreciated ‘both compositions must be present’ aspect.

1 Like
34

I am using EhrBase as my CDR

35

Yes, that’s the requirement

2 Likes