Calling Sub-Queries with the EX Keyword

DBMS system tables may store information to be reversed in columns with LONG, BLOB, TEXT and other incompatible data types, which PowerDesigner cannot directly concatenate into strings.

You can bypass this limitation by using the EX keyword and creating user-defined queries and variables in the existing reverse engineering queries with the syntax:

%UserDefinedQueryName.UserDefinedVariableName%

These user-defined variables are evaluated by sub-queries that you write.

In the following example, the value of OPTIONS is marked as containing a user-defined query, and we see in the body of the query that the 'global partition by range' option contains a user-defined query called :'SqlPartIndexDef', which seeks values for the variables 'i.owner' and 'i.index_name':

{OWNER, TABLE, CONSTNAME, OPTIONS EX}

select
 c.owner,
 c.table_name,
 c.constraint_name,
	...
	'global partition by range
		(%SqlPartIndexDef.'||i.owner||i.index_name||'%)', 
	...
Note: Extended queries are not be added to the ReversedQueries item.
  1. A query is executed to evaluate variables in a set of string statements. If the EX keyword is present in the query header, PowerDesigner searches for user-defined queries and variables to evaluate. You can create user-defined queries in any live database reverse engineering query. Each query must have a unique name.
  2. The execution of the user-defined query generates a resultset containing pairs of user-defined variable names (without %) and variable value for each of the variables as needed. For example, in the following resultset, the query returns 3 rows and 4 columns by row:

    Variable 1

    1

    Variable 2

    2

    Variable 3

    3

    Variable 4

    4

    Variable 5

    5

    Variable 6

    6
  3. These values replace the user-defined variables in the original query.
Parent topic: Live Database Reverse Engineering