Tilda JSON Syntax: View Where Clause

<-- View Syntax

Where Clauses

Views often specify a where clause to filter the final results. A where clause looks like the following:

"subWhereX":{
  "clause":["     com.myCo.patients.data.Patients.Order.visitRefnum is not null"
           ," and com.myCo.patients.data.Patients.Order.ordered is not null"
           ," and com.myCo.patients.data.Patients.Order.canceled is null"
           ," and com.myCo.patients.data.Patients.Order.voided is null"
           ]
 ,"description":["For an Order to be considered, it must:<UL>"
                ,"<LI>be associated to a visit,</LI>"
                ,"<LI>have an ordered date,</LI>"
                ,"<LI>not be canceled,</LI>"
                ,"<LI>not be voided</LI>"
                ,"</UL>"
                ]
}


"subWhereX":{
  "clause":["    com.myCo.patients.data.Patients.Visit.status in ('X', 'Y', 'Z')"
           ,"AND com.myCo.patients.data.Patients.Visit.start >= '2015-01-01'"
           ,"AND date_part('year', com.myCo.patients.data.Patients.Visit.start) <= date_part('year', current_date)"
           ]
 ,"description":["For a visit to be considered, it must:<UL>"
                ,"<LI>have a status = 'X', 'Y' or 'Z',</LI>"
                ,"<LI>have started between Jan 1st 2015 and the end of this current year.</LI>"
                ,"</UL>"  
               ]
}

The main fields are:

• subWhereX: The where clause definition.
• clause: a fragment of SQL that would work in a where clause.
• description: An HTML fragment for the description of what the filter does.

🎈 NOTE: In an earlier version of Tilda, there was a "subWhere" element which only specified the where clause without any documentation. It was decided to deprecate that feature and use "subWhereX", where "X" stands for "eXtended".

🎈 NOTE: Tilda will parse the clause and requires the fully qualified "<package>.<schema>.<table>.<column>" syntax to reference any given column.