Examples

Single Table Query With Complex Search Expression

Query:

SELECT * FROM encounters E WHERE (E.admit_doctor = E.consult_doctor AND E.total_charges > (E.credit_limit * E.discount)) OR UPPER(E.Patient_name) = 'JOE'

1. Check the Process Order of the table being processed using dam_getInfo(DAM_INFO_QUERY_PROCESS_ORDER).

   Get the join size using dam_getInfo(DAM_INFO_JOIN_QUERY_SIZE). In this case JOIN_QUERY_SIZE will be 1 and QUERY_PROCESS_ORDER will be 0. The IP detects that it is a single table query.

2. The IP will call dam_describeTableByProcessOrder() to get details of the table in the query.

   dam_describeTableByProcessOrder(iTableProcess Order=0) will return encounters table information and the TableNum of the encounters table will be returned as 0.

3. The IP can begin to get the search condition (filter condition) for the encounters table.

   dam_getTableSearchExp(iTableNum=0) returns:

   SearchExpression: (E.admit_doctor = E.consult_doctor AND E.total_charges > (E.credit_limit * E.discount)) OR UPPER(E.Patient_name) = 'JOE'

   Join Expression: NULL

   JoinType: N/A

Now you will see how the damex() API returns the details of the same search expression.

Query:

SELECT * FROM encounters E WHERE (E.admit_doctor = E.consult_doctor AND E.total_charges > (E.credit_limit * E.discount)) OR UPPER(E.Patient_name) = 'JOE'

1. damex_describeLogExp(SearchExp) will return the iType as OR and return LeftLogExp1, RightLogExp1.
   a. damex_describeLogExp(LeftLogExp1) will return the iType as AND and return LeftLogExp2 and RightLogExp2.
   b. damex_describeLogExp(LeftLogExp2) will return the iType as COND and the condition as cond1 which will hold E.admit_doctor = E.consult_doctor.
   c. damex_describeLogExp(RightLogExp2) will return the iType as COND and the condition as cond2. cond2 will hold E.total_charges > (E.credit_limit * E.discount).
   d. damex_describeLogExp(RightLogExp1) will return the iType as AND and return cond3. cond3 will hold UPPER(E.Patient_name) = ‘JOE’.
   e. dam_describeCondEx(cond1) will return EQUAL condition and LeftValExp1 and RightValExp1. LeftValExp1 will be a COLUMN referring to admit_doctor. RightValExp1 will be a COLUMN referring to consult_doctor.
   f. dam_describeCondEx(cond2) will return GREATER condition and LeftValExp2 and RightValExp2. LeftValExp2 will be a COLUMN referring to total_charges. RightValExp2 will be arithmetic expression of type MULTIPLY and return LeftValExp3 and RightValExp3. LeftValExp3 referring to column credit_limit and RightValExp3 will refer to column discount.
   g. dam_describeCondEx(cond3) will return EQUAL condition and LeftValExp4 and RightValExp4. dam_describeValExp(LeftValExp4) will return scalar function UPPER() and the arguments would have value of column Patient_name. RightValExp will be a literal value of type CHAR with value 'JOE'.
2. The IP converts the search expressions to the format required by the back-end and executes it.
3. Build results of matching encounters records using the standard OpenAccess SDK SQL engine functions for allocating and building rows.