Can I do a UNION in Lookup Query?

Document ID : KB000022223
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

We are attempting to create a custom Dynamic Query Lookup using a 'UNION' statement. Here is our example and the observations we have made with the behavior of this syntax.
Can you please tell us why it is working this way? Is the syntax correct?

This statement will allow the lookup to display a list of values with one row added to show 'No Subject' null value.
(NOTE: We found that you cannot place the @FILTER@ keyword in the second select statement for the union, the Lookup will not get registered if you attempt to do so.)

 
     SELECT @SELECT:incident_code:incident_code@,
     @SELECT:subject:subject@
     FROM imm_incidents
     WHERE @FILTER@
     UNION
     SELECT NULL, 'No Subject'
     FROM DUAL

It is interesting to note the behavior of defining a Dynamic Query Lookup as shown.

  1. When using the lookup on the custom attribute - when you select a valid value and save the record, the value you have selected will be saved properly within the database, however, the 'All' value will appear on the properties page ; this is caused by not having the @FILTER@ keyword. The application will always display this row and ignore the filter criteria.

  2. If this lookup is attached to a custom attribute defined as a multi-valued lookup, a red, system error message may appear to the end-user when attempting to save a value.

  3. If you attempt to use the custom attribute in a power filter, the expression will appear correctly in the Expression box, however, in the Evaluated Expression text, it will display the 'All' value ; this is caused by not having the @FILTER@ keyword. The power filter will return the correct results, but because the row created by the union statement is not supported, it gives misleading information.

  4. If you attempt to use the custom attribute in a power filter and try to set the expression to use the null value defined by the lookup query, the application will generate the following error message: ODF-0011: Value is required when adding to expression.

Solution:

The definition of the Dynamic Query Lookup is using a 'UNION' statement that is not supported with the @FILTER@ keyword unless the UNION statement is embedded within a sub-query or database view.

To utilize UNION statements in a Dynamic Lookup Query Definition, the statement must be enclosed within a sub-query or placed into a database view. The Dynamic Lookup Query Definition must also include the @FILTER@ keyword for it to work properly.

To create the correct syntax for this Dynamic Lookup Query Definition using a sub-query, use the following example:

  
     SELECT @SELECT:imm.incident_code:incident_code@,
            @SELECT:imm.subject:subject@
     FROM 
     ( SELECT incident_code, subject
       FROM imm_incidents
       UNION
       SELECT NULL, 'No Subject'
        FROM DUAL
     ) imm
     WHERE @FILTER@

Another alternative is to create a Database 'view' and use it in the Dynamic Lookup Query Definition. For example if you created a database 'view' that utilizes the sub-query in the example above, and named the view IMM_V, you can use it in the Dynamic Lookup Query Definition as follows:

 
     SELECT @SELECT:imm_v.incident_code:incident_code@,
            @SELECT:imm_v.subject:subject@
     FROM imm_v
     WHERE @FILTER@

Keywords: CLARITYKB, how-to, configuration, lookup union statement