SQL Query Fields

Three types of SQL Query fields exist:

• SQL Query - Single Response Dictionary, a query without a parameter creating a single selection dictionary.
• SQL Query - Multiple Response Dictionary, a query without a parameter creating a multiple selection dictionary.
• SQL Query - Custom Search, a query with single parameter creating a single selection dictionary.  

These prompts create dynamic dictionaries based upon other tables where data may be deleted and purged. Consequently there is no guarantee of consistently producing the same results when returning to edit a data row with these prompts.

Initially, simple queries fetching a single value as both dictionary code and dictionary value were supported. RADplus2014 Update #059 added support for larger queries with JOIN and UNION clauses fetching up to two values for distinct dictionary codes and dictionary values.
The two value syntax is SELECT value1, value2 INTO :CODE, :VALUE FROM Schema.Table_Name.  

Only one value, dictionary code, submits into the database. All three types support two value functionality, but for SQL Query - Custom Search this is not recommended. SQL Query - Custom Search type is dependent on an entered search parameter which isn't submitted to the database. The results from SQL Query - Custom Search execution can be retained and referenced while a user is in the same form session as the search execution; outside that form session the query cannot be recreated to recover the dictionary value. Consequently, SQL Query - Custom Search will not project dictionary value in Chart View or inquiries. This is true even when Chart View is opened while the form session remains opened since the Chart View is a separate process where search execution did not occur.

Additionally only dictionary code and not dictionary value appears in SQL projection of these prompt types.