Tags

    Conditional Query

    Queries used by a QFIND or QGROUP need to built in an Access/Table, but may use values set by a user. Webtemplates submissions can be evaluated by Qilan and passed directly to query icons. Consider this query...

    Untitled.tiff






    The values, ymd_number (formatted as an integer yyyymmdd) and staff_rowid (integer) are database columns. Our query will use these values to return a result. Furthermore, the user may or may not enter a staff_rowid, thus we want the query to use ymd_number regardless. This is what is referred to as 'conditional'.







    The field values, report_start_date and report_stop_date are framework fields. A user will be entering these values as dates, but our query requires them to be in the format of yyyymmdd. Additionally, the report_start_date, by our report design, will always be the first day of the month. Since the column value of ymd_number is an integer, a case conversion is made before the value is acquired by the query.





    The third query element uses an IF-THEN-ELSE operator. While the operator itself is not passed to the database, the result is. Staff_rowid_search is a framework value submitted by the user. If it is undefined, we want the query to ignore it; otherwise the staff_rowid_search is to be used by the query. This is done by testing for the presence of a value at the framework level by acquiring the abacus above. Finally, if no value exists, we use a query that selects ALL records. Using the primary key (rowid) accomplishes this.





    A couple of notes about this type of construction. A query icon cannot contain empty or undefined elements. This will return SQL errors by the database. In our example, report_stop_date cannot be undefined as no provision has been made for this event.

    The AND conjunction is expanded. One AND operator is used and each parameter is expanded. This approach is recommended as it highly optimized, although multiple operators will work.

    Lastly, this type of conditional query can be expanded as your designs require. There is no practical limit.

    Comments

    /groups/commonground/search/index.rss?tag=hotlist/groups/commonground/search/?tag=hotWhat’s HotHotListHot!?tag=hot0/groups/commonground/sidebar/HotListNo items tagged with hot.hot/groups/commonground/search/index.rss?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcomelist/groups/commonground/search/?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcomeRecent ChangesRecentChangesListUpdates?sort=modifiedDate&kind=all&sortDirection=reverse&excludePages=wiki/welcome0/groups/commonground/sidebar/RecentChangesListmodifiedDateallRecent ChangesRecentChangesListUpdateswiki/welcomeNo recent changes.reverse5search