select
a_a/data[at0002]/events[at0003]/data[at0001]/items[at0004]/value/magnitude as Temperature_magnitude,
e/ehr_id/value,
a_a/data[at0002]/events[at0003]/time/value as Time
from EHR e
contains COMPOSITION a
contains OBSERVATION a_a[openEHR-EHR-OBSERVATION.body_temperature.v1]
where
Temperature_magnitude > 37 and
Time>='2017-10-14T09:35:30+00:00'
In SQL, it is not possible to have Aliases inside WHERE clauses. I the spec, we donāt say much about aliases at the moment: " As in SQL, AQL supports the use of a name alias for the retrieved data. This is done with the keyword AS , followed by the name which conforms to the syntax rule of AQL variable."
Any strong opinions on this? In EHRbase such a query would fail at the moment
It is(or was) not part of the original AQL spec but it has been introduced by Better, and I think worth supporting. The more we can avoid raw paths, the better.
I donāt tend to use it, precisely because I know it is not supported widely but I would if I could!!
It is not a big deal for us āinsidersā but it is just one of these tiny pain points that we should remove if we can.
Just wonder perhaps the aliases could be replaced with paths while translating AQL into proper SQL. Agree with Ian, these names make the query a lot easier to read.
Iām trying to find out why this is not supported in SQL. I just want to make sure there is no hassle with Subselects etc. that might be introduced eventually to AQL.
From MySQL doc: " Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined."
From stackexchange: " Thatās according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:
I asked exactly the same question of Ian the other day - and I think we should support aliases in the WHERE clause for the very obvious reason that there should never be copies of some complicated programming element, since in maintenance, one can forget to modify both (if correcting an error), and then later on you end up with a query with two different paths, meant to be the same, and no-one can easily figure out which is right.
Not necessarily a strong opinion but itād require SELECT clause aliases to be unique to avoid ambiguity.
Other than that, I cannot see any issues at the Aql level. It may give engine implementers a bit of an exercise but no red flags in that department in my head, though not much capability left in it these daysā¦
While I was reviewing our AQL text about aliases, and the ANTLR4 grammar, I had same question myself: why donāt we supported - in bumped into same (my)SQL explanation. I assumed our AQL engines have same order of processing things (i.e. first FROM, than WHERE, than SELECT) - therefore I did not changed things.
One thing to note, the way grammar is now will allow also aliases to be used inside the WHERE (although is not formally specified).
However, these aliases names should follow AQL variable names syntax: must be unique within an AQL statement, formed of an initial letter followed by any number of alphanumerics and underscores, as long as it does not clash with an reserved words.
In your example I see use of quote, spaces inside the name - both would be illegal. Also the use of ā+ā is not supported by specs.
Specs are however possible to be changed - as long as we have proposal and unanimity we can still do it for v1.1.
The important point is in the example is that there is not a defined bahaviour once we use the āEMPLOYEE NAMEā in the WHERE clause (sorry for being lazy not to edit the example I stole from the internet). However, we might want to enhance the AQL syntax to allow the ā+ā which then would cause trouble down the road if aliases are allowed at the same time.
Would it really cause an issue ? You could just say compound aliases not supported (iām sure there is proper name!) but would it not be easy enough to support that kind of concatenation in the where clause and actually in the ORDER BY clause too.
Found this thread, were aliases in where supported in the end? Just realized I wanted to do
Select e/ehr_id/value,count(pd/data[at0001]/items) as cuenta from EHR e contains EVALUATION pd[openEHR-EHR-EVALUATION.problem_diagnosis.v1] where cuenta >1
PS: Iām still trying to find alternative ways of solving the āget me the ehr_ids with more than one conditionā query
we can still do it for v1.1.
