Code Block

Code Block defines data sources, structures, and relationships, as well as instructions for data loading, refreshing, and deletion.

Syntax

code_block ::= [ builtin_schema_statement | template_schema_statement ] 
 		rowsource_specification [column_mapping_assignment]... 
 		[ default_row_specification ] 
 		[ aggregate_specification ] 
 		[ primary_key_specification ] 
 		[ entity_id_specification ] 
 		[ incremental_refresh_directive ]
 		[ delete_specification ]...

Schema Statements

Example:

builtin_schema_statement | template_schema_statement

For details about schema statements, refer to Schema Definition.

Row Source Specification

Syntax

rowsource_specification ::= ROWSOURCE { table_source | dataset_source | inline_source }

table_source ::= rowsource_expression

rowsource_expression ::= table_reference join_condition... [ row_filtering_conditional ]

join_condition ::= {join_type table_reference ON join_expr | join_type_cross table_reference }

join_type ::= { INNER | LEFT OUTER | RIGHT OUTER | FULL OUTER } JOIN
join_type_cross ::= CROSS JOIN

join_expr ::= boolean_returned_expression 

row_filtering_conditional ::= WHERE boolean_returned_expression

dataset_source ::= { UNION | UNION-ALL } table_reference_list 
table_reference_list ::= '[ ' table_reference [ , table_reference ]... ' ]'
table_reference ::= table_name | table_alias

inline_source ::= INTABLE '( ' column_and_type inline_value ' )' 
column_and_type ::= '[ ' column_name : data_type [, column_name : data_type ]... ' ]'

inline_value ::= VALUES '( ' row_expression [, row_expression ]... ' )' 
row_expression ::= '[ ' constant_value [, constant_value ]... ' ]’

Example: Inner Join

IMPORT SOURCE CUSTOMERS
IMPORT SOURCE COUNTRIES

DEFINE VERSIONED DATASET GAMING_CUSTOMER_REACHED_G
 ROWSOURCE CUSTOMERS INNER JOIN COUNTRIES ON (CUSTOMERS.COUNTRY_ID = COUNTRIES.COUNTRY_ID);
 THIS = COUNTRIES[COUNTRY_REGION,COUNTRY_SUBREGION];
 THIS = CUSTOMERS;
 PRIMARYKEY[CUST_ID];
END

Set Operations

A set operation works with two or more datasets and combines them into a single result set as a ROWSOURCE.

• You're required to specify column-mapping to define the structure of the resulting table.
• You need to explicitly specify a primary key if you want one, because there's no default primary key.

Example: UNION

IMPORT SOURCE PRODUCTS
DEFINE DATASET PRODUCTS_D FROM PRODUCTS[PROD_ID,PROD_NAME,PROD_EFF_FROM,PROD_EFF_TO,PROD_VALID] END
DEFINE PRIVATE DATASET PC_PRODUCTS_TMP
 ROWSOURCE PRODUCTS WHERE
 PRODUCTS.PROD_CATEGORY = 'Hardware' AND PRODUCTS.PROD_SUBCATEGORY LIKE '%PCs';
 THIS = PRODUCTS[PROD_ID,PROD_NAME,PROD_CATEGORY_DESC,PROD_LIST_PRICE,PROD_MIN_PRICE];
 THIS[PRIORITY] = 2;
END

DEFINE PRIVATE DATASET CONSOLE_PRODUCTS_TMP
 ROWSOURCE PRODUCTS WHERE PRODUCTS.PROD_SUBCATEGORY_DESC LIKE '%Game%' AND PRODUCTS.PROD_VALID = 'A';
 THIS = PRODUCTS[PROD_ID,PROD_NAME,PROD_CATEGORY_DESC,PROD_LIST_PRICE,PROD_MIN_PRICE];
 THIS[PRIORITY] = 1;
END

DEFINE DATASET ENTERTAINMENT_PRODUCTS_C
 ROWSOURCE UNION[PC_PRODUCTS_TMP,CONSOLE_PRODUCTS_TMP];
 THIS = PC_PRODUCTS_TMP;
 PRIMARYKEY[PROD_ID];
END

INLINE Data Set

An inline table defines both its structure and its data, including a predefined set of records, directly within the table definition. Use inline tables to embed small datasets in your code without relying on external sources.

Example:

DEFINE INLINE DATASET MYINLINEDATA
 ROWSOURCE INTABLE([
    PROMO_CATEGORY_ID:VARCHAR2(128),
    PROMO_CATEGORY:VARCHAR2(60),
    PROMO_DISCOUNT_RATE:BIGDECIMAL(38, 12)]
    VALUES
       ([1 ,'No Promotion',0],
       [2 ,'Television',11.5],
       [3 ,'Internet', 15.4])
    );
PRIMARYKEY[PROMO_CATEGORY_ID];
END

Note:

You could directly insert numeric values into a VARCHAR2 column with or without enclosing them in quotes.