DESIGNER_ADD_DESIGN_QUERY

Reads and parses the specified query, and if accepted, adds it to the design. Before you add queries to a design, you must add the queried tables with DESIGNER_ADD_DESIGN_TABLES.

All accepted queries are added to the system table DESIGN_QUERIES.

Behavior Type

Immutable

Syntax

DESIGNER_ADD_DESIGN_QUERY ( 'design‑name', 'design‑query' [, query‑weight] )

Parameters

design‑name

Name of the target design.

design‑query

Executable SQL query.

query‑weight

Optionally assigns a weight to each query that indicates its importance relative to other queries, a real number >0 and ≤ 1. Database Designer uses this setting to prioritize queries in the design .

If you omit this parameter, Database Designer assigns a weight of 1.

Privileges

Non-superuser: design creator with all privileges required to execute the specified query

Errors

Database Designer returns an error in the following cases:

  • The query contains illegal syntax.
  • The query references:
    • External or system tables only
    • Local temporary or other non-design tables
  • DELETE or UPDATE query has one or more subqueries.
  • INSERT query does not include a SELECT clause.
  • Database Designer cannot optimize the query.

Examples

The following example adds the specified query to the VMART_DESIGN design and assigns that query a weight of 0.5:

=> SELECT DESIGNER_ADD_DESIGN_QUERY (
   'VMART_DESIGN',
   'SELECT customer_name, customer_type FROM customer_dimension ORDER BY customer_name ASC;', 0.5 
   );

See Also

Running Database Designer Programmatically