/*+ FORCE_DATATYPE( dataTypeDef ) */
- dataTypeDef: A data type definition which will be applied to the designated column. This can be a Cirro data type or a type native to the system executing the query. Specify a native data type only when the data movement destination is known. Otherwise, specify a Cirro data type. When specifying a native data type, enclose in single quotes.
SELECT column_expression [ /*+ FORCE_DATATYPE ( dataTypeDef ) */] [ ,column_expression [ /*+ FORCE_DATATYPE ( dataTypeDef ) */ ] ...n] FROM fromClause;
This hint does not apply any kind of explicit casting to the column data, although implicit casting is possible when the underlying database platform supports it.
- Implicit casting could occur, for example, when a VARCHAR(10) being moved from another data source in the federation is inserted into a database column field where the user has forced the column’s data type to be a VARCHAR(100). In most databases, this is an acceptable implicit conversion.
This hint is applied only to the data types in temporary tables created during data movement. If this hint is included in a query that does not require data movement, it will be ignored.
This hint is often used in combination with functions that cast/convert or otherwise transform the data prior to it being moved to another system.
Define a forced data type for a specific column only once within a query; if multiple and conflicting data type definitions are provided for a single column, the query engine will determine which data type to use.
Simple SELECT example
SELECT c1.customer_id, c1.first_name, c1.last_name /*+ FORCE_DATATYPE( varchar(100) ) */, c2.marital_status FROM MYSQL1.inventory.inventory.customer c1 INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2 ON c1.customer_id = c2.customer_id;
Multiple column example with a subquery
SELECT c3.customer_id, c3.first_name, c3.last_name /*+ FORCE_DATATYPE( VARCHAR(100) ) */, c3.marital_status /*+ FORCE_DATATYPE( 'CHAR(1)' ) */, v.visit_date, v.visit_url FROM (SELECT c1.customer_id, c1.first_name, c1.last_name /*+ FORCE_DATATYPE( 'VARCHAR2(100)' ) */, c2.marital_status FROM MYSQL1.inventory.inventory.customer c1 INNER JOIN ORACLE1.ORACLE.CIRRO.cust c2 ON c1.customer_id = c2.customer_id c3 INNER JOIN HIVE1."default"."default".visits v ON c3.customer_id = v.customer_id