OPTION Clause

The OPTION keyword denotes options which can be passed with the command. These options are specific to the CData Virtuality Server and not covered by any SQL specification.

Usage

SQL

OPTION option (option)*

Supported Options

MAKEDEP

This option specifies source tables that should be made dependent in the join.

Usage

SQL

MAKEDEP table [(,table)*]

MAKENOTDEP

This option prevents a dependent join from being used.

Usage

SQL

MAKENOTDEP table [(,table)*]

NOCACHE

This option prevents cache from being used for all tables or the given tables. Note that all tables specified in the OPTION clause should be fully qualified.

Usage

SQL

NOCACHE [table (,table)*]

$NOOPT

If this option is set to TRUE (or if the modifier is omitted), no recommended optimizations are created for queries. Already created optimizations will still be used. Default: FALSE.

Usage

SQL

$NOOPT [FALSE | TRUE]

$NOFAIL

This option prevents a command from throwing an error in case of failure. The error will be silently ignored.

It is processed only when applied to the following procedures:

createCopyOverSQLJob()
createCopyOverSourceTableJob()
createGatherStatisticsJob()
createOptimizationJob()
createincrementaloptimizationjob()
createSQLJob()
setCurrentDwh()
setRemark()
CREATE OR REPLACE VIEW
CREATE OR REPLACE PROCEDURE

The CData Virtuality Exporter adds "OPTION $NOFAIL" for exported calls to:

setRemark()

Usage

SQL

$NOFAIL [FALSE | TRUE]

If the required value is TRUE, you do not need to specify it explicitly, as shown in the example below.

Example

SQL

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

$PREFER_DWH

This option may take one of the following values:

FORCE (default): 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 then 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

Note that if the join is already materialized, it is served from the analytical storage, regardless of the option value

Usage

SQL

$PREFER_DWH [FORCE | ALWAYS | CONDITIONAL | NEVER]

Example

Example for join within the same schema (data source):

SQL

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

$ALLOW_CARTESIAN

This option may take one of the following values:

NEVER: never do cartesian product
IMPLICIT (default): only allow cartesian 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 do not have common SELECT criteria, the query processor may generate a cross join
ALWAYS: always allow cartesian

Usage

SQL

$ALLOW_CARTESIAN (NEVER | IMPLICIT | ALWAYS)

Examples

1. Example of queries with an explicit cross join (works when the $ALLOW_CARTESIAN option is set to ALWAYS):

SQL

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

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

SQL

SELECT * FROM (CALL 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

This is an XMLQuery, XMLTable streaming mode specific option and it allows handling elements that contain only whitespaces. Elements that contain only whitespaces are handled as empty if this option is not enabled

Usage

SQL

$ALLOW_XML_TEXT_NODE_WHITESPACE [FALSE | TRUE]

Example

Examples of an 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
*/

Previous versions of the CData Virtuality Server accepted the PLANONLY, DEBUG, and SHOWPLAN option arguments. These are no longer accepted in the OPTION clause.

In addition to providing an option for an SQL statement, it is possible to set it globally. Please see the Controlling Data Virtuality Server Behaviour chapter for more details.