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

[ian.mcnicoll]

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'} ```

[Sravanthi_Kovi]

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

[Seref (Seref Arikan)]

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)

[Sravanthi_Kovi]

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

[ian.mcnicoll]

@Sravanthi_Kovi

SELECT 
    e/ehr_id/value as ehrId,
   c/archetype_details/template_id/value as templateId,
  c/context/start_time/value as startTime,
 e1/data[at0002]/items[at0022]/value/mappings/target/code_string
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' OR  c/archetype_details/template_id/value='temp2')
 AND 
    e1/data[at0002]/items[at0022]/value/mappings/target/code_string='snomed_code' 
    ORDER BY c/context/start_time/value DESC"

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.

[pablo (Pablo Pazos)]

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.

[Sravanthi_Kovi]

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

[ian.mcnicoll]

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

Ian

[Sravanthi_Kovi]

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

[pablo (Pablo Pazos)]

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

[ian.mcnicoll]

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?

[pablo (Pablo Pazos)]

@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

[ian.mcnicoll]

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

[Sravanthi_Kovi]

I am using EhrBase as my CDR

[Sravanthi_Kovi]

Yes, that’s the requirement