Subqueries or WITH Clauses

WOW has limited support for subqueries and the SQL WITH clause. WOW does not support prompting with ? support within a subquery or inside the WITH clause.

However, the following technique can be used to enable prompting inside the WITH clause:

Assuming there is more to the SELECT in the WITH and the main SELECT you could alter your SQL to still show a prompt, but have it behave the same and return the same results.

Example

WITH f1 AS (

SELECT kvste FROM library.kgsvy WHERE kvste = ??1

)

SELECT kvste FROM f1 WHERE (1 = 1 OR kvste = ?28073)

Notes

• • This logic renders the WHERE clause of the main SELECT not applicable from the 1=1, but WOW will recognize the prompt for 28703. And WOW supports double question marks within the WITH, so the ??1 would get what ever value the user entered for the 1st prompt.

  • Also, be sure to include SQLContext { tables:<library>.kgsvy; } in the Operation Properties, so WOW knows where to look for FieldDescriptors.

References:

- SQL & Field Descriptor Prompt (see section on: Field Descriptor Prompt Parameters)

15. Parameters: Types & Definitions