H VIEW QUERY

View query enables you to define views using query language and create views using the dataset definition syntax.

In Data Augmentation Scripts, you create a query file with the .qry extension (Example: <name>.qry). This file include the queries to create the views in files.

For Autonomous Data Warehouse, this is similar to the select syntax in Oracle.

Syntax

raw_view_dataset ::= DEFINE RAWVIEW DATASET dataset_name [raw_view_type] view_specification END 
raw_view_type ::= RAWVIEWTYPE = 'string'
String should be a valid DB name e.g., ORACLE. Default is ORACLE. (Note: Currently only ORACLE is supported.)
view_specification ::= RAWSQL = GETSQL'(' '"' query_file_name'"' ')';

Note:

• Write valid syntax. Data Augmentation Scripts doesn't parse the queries in the query file for invalid syntax.
• Use full names and fully qualified prefixes for the local tables in the view query because input tables used in queries can be local datasets.
• View query must not end with ';'.

Example

Write the view query: channels.qry:

SELECT 
CHANNELS_D.CHANNEL_CLASS,CHANNELS_D.CHANNEL_CLASS_ID,CHANNELS_D.CHANNEL_DESC,CHANNELS_D.CHANNEL_ID,TRUNC(SYSDATE) AS CURDATE 
FROM DW_LOCODE_X_APP_CHANNELS_D CHANNELS_D

Reference the query file in code file (.hrf) main.hrf :

DEFINE DATASET CHANNEL_VRAWVIEW
RAWVIEWTYPE = ORACLE;
RAWSQL = GETSQL("channels.qry");
END