OPTION Clause

The OPTION keyword denotes options the user can pass with the command. These options are specific to the Data Virtuality Server and not covered by any SQL specification.

Usage

SQL

OPTION option (option)*

Supported Options

To view the full table, click the expand button in its top right corner

Option	Usage	Description
`MAKEDEP`	`MAKEDEP table [(,table)*]`	Specifies source tables that should be made dependent in the join
`MAKENOTDEP`	`MAKENOTDEP table [(,table)*]`	Prevents a dependent join from being used
`NOCACHE`	`NOCACHE [table (,table)*]`	Prevents cache from being used for all tables or the given tables. All tables specified in the `OPTION` clause should be fully qualified
`$NOOPT`	`$NOOPT [FALSE \| TRUE]`	If `TRUE` (or if the modifier is omitted), no recommended optimizations are created for queries. Already created optimizations will still be used. Default: `FALSE`
`$NOFAIL`	`$NOFAIL [FALSE \| TRUE]`	Prevents a procedure from throwing an error in case of failure. Error will be silently ignored. Processed only when applied to: `createCopyOverSQLJob()` `createCopyOverSourceTableJob()` `createGatherStatisticsJob()` `createOptimizationJob()` `createincrementaloptimizationjob()` `createSQLJob()` `setCurrentDwh()` `setRemark()` The Data Virtuality Exporter adds "`OPTION $NOFAIL`" for exported calls to: `setRemark()`
`$PREFER_DWH`	`$PREFER_DWH (FORCE \| ALWAYS \| CONDITIONAL \| NEVER)`	`FORCE` (default value): analytical storage optimizations are applied in any case. If optimization is enabled, but no materialized tables can be used for it (e.g. materialized table not found in the analytical storage), an exception is thrown; `ALWAYS`: analytical storage optimizations are applied whenever possible. If optimization is enabled and the corresponding materialized table is available in the analytical storage, such a materialization will be used; if the materialized table is not available, the query is automatically redirected to the source; if both source and materialized tables are not available, an exception is thrown; `CONDITIONAL`: the join queries directed to the same data source will be calculated by the original data source system, even if some of the tables (tableA or tableB or both) are already pre-materialized in the analytical storage (original data is preferred to analytical storage to speed up the join); `NEVER`: optimizations will not be applied under any circumstances; Please note that if the join is already materialized, it is served from the analytical storage, regardless of the option value
`$ALLOW_CARTESIAN`	`$ALLOW_CARTESIAN (NEVER \| INTERNAL \| IMPLICIT \| EXPLICIT \| ALWAYS)`	`NEVER`: never do cartesian product; `INTERNAL` (default value): never do cartesian product except for special procedural joins; `EXPLICIT`: only allow if explicitly present in SQL query; `IMPLICIT`: only allow if implicitly generated by the query processor: the query may not contain explicit joins, but in some cases, for complex queries, when a pair of `ACCESS` nodes don't have common select criteria, the query processor may generate a cross join; `ALWAYS` – always allow cartesian
`$ALLOW_XML_TEXT_NODE_WHITESPACE`	`$ALLOW_XML_TEXT_NODE_WHITESPACE [FALSE \| TRUE]`	XMLQuery, XMLTable streaming mode specific option. Allows handling elements that contain only whitespaces. Elements that contain only whitespaces are handled as empty if this option is not enabled

Examples

$NOFAIL

SQL

CALL "SYSADMIN.setRemark"("name" => 'views.doesnotexist', "remark" => 'this is a new comment') OPTION $NOFAIL;;

$PREFER_DWH

In this example, the join is performed within the same schema (data source):

SQL

SELECT <sth.> FROM schemaA.tableA <some_join> schemaA.tableB ON <some_condition> ...

$ALLOW_CARTESIAN

1. Examples with a query with an explicit cross join:

SQL

SELECT * FROM SchemaA.TableA, SchemaB.TableB
SELECT * FROM SchemaA.TableA CROSS JOIN SchemaB.TableB

2. Example of a query with an internal cross join:

SQL

SELECT * FROM (EXEC file.getFiles('1.csv')) f, TEXTTABLE(TO_CHARS(f.file,'ISO-8859-15') COLUMNS ...)

3. Example of a query which results in an implicit cross join:

SQL

SELECT 	CAST((SUM((CASE WHEN (("order_position"."state" = 512) OR ("order_position"."state" = 1024)) THEN 1 ELSE 0 END)) - SUM((CASE WHEN ("order_position"."state" = 512) THEN 1 ELSE 0 END))) AS FLOAT) AS "usr_Retourenquote_St_ck_Kopie_qk"
FROM "test_dvcore301_1"."public.order_position" "order_position"
INNER JOIN "test_dvcore301_2"."public.customer_order" "customer_order" ON ("order_position"."order_id" = "customer_order"."id")
INNER JOIN "test_dvcore301_1"."public.address" "address" ON ("customer_order"."shipping_address_id" = "address"."id")
INNER JOIN "test_dvcore301_2"."public.supplier_article" "supplier_article" ON ("order_position"."supplier_article_id" = "supplier_article"."id")
INNER JOIN "test_dvcore301_2"."public.article" "article" ON ("order_position"."article_id" = "article"."id")
INNER JOIN "test_dvcore301_2"."public.article_details" "article_details" ON ("order_position"."supplier_article_id" = "article_details"."id")
INNER JOIN "test_dvcore301_2"."public.principal" "principal" ON ("customer_order"."customer_id" = "principal"."id")
INNER JOIN "test_dvcore301_2"."public.principal" "stylist" ON ("customer_order"."stylelist_id" = "stylist"."id")
WHERE ((("article_details"."category" IS NULL) OR (("article_details"."category" >= '') AND
("article_details"."category" <= 'Some Category'))) AND (("article_details"."season" IS NULL)
OR (("article_details"."season" >= 'Season') AND ("article_details"."season" <= 'Season2')))
AND (("customer_order"."date_shipped" >= {ts '2012-02-02 18:00:00'}) AND ("customer_order"."date_shipped" <= {ts '2013-02-14 17:27:08.222000'}))) HAVING (COUNT(1) > 0)
OPTION $NOOPT

$ALLOW_XML_TEXT_NODE_WHITESPACE

Examples of am XMLTABLE query with the option enabled:

SQL

SELECT
   xt.NAME,
   xt.SURNAME,
   LENGTH(xt.SURNAME) AS SURNAME_LENGTH
FROM XMLTABLE(
       '/root/band_members/band_member'
       PASSING XMLPARSE(
       DOCUMENT
           '<root>
               <band_members>
                   <band_member>
                       <name>George</name>
                       <surname> </surname>
                   </band_member>
                   <band_member>
                       <name>Ringo</name>
                       <surname></surname>
                   </band_member>
               </band_members>
           </root>'
   )
   COLUMNS
       NAME STRING PATH 'name',
       SURNAME STRING PATH 'surname'
) xt
OPTION $ALLOW_XML_TEXT_NODE_WHITESPACE;;
-- returns:
/*
NAME	SURNAME	SURNAME_LENGTH
George	 	1
Ringo		0
*/