9.7 Generate Data Flow From SQL Statements

You can use SQL statements to generate data flows using the SQL parser feature of Data Transforms.

You can provide a representation of a data flow that has target data entity and source data entity by having both sections insertion and selection in the SQL statement. The Data Transforms SQL parser feature will analyze the SQL statement, convert it to a data flow, and display it on the Design Canvas.

The SQL parser supports the following operators:

• Data entities: Data entities are generated based on where they are referenced within the SQL statements received as input by the SQL Parser.
• Expression: Expression components are generated when a function is identified in the SQL statement. However, this type of component is not generated if the function is an aggregation. In such cases, it is replaced by an Aggregation component. Expression components are also generated if the SQL Parser component finds arithmetic or logic expressions within the statement.
• Aggregation: Aggregation components are generated exclusively when aggregation functions are detected in the SQL statement. These components can be created regardless of whether they include other aggregation operators, such as GROUP BY and HAVING.
• Filter: Filter components are generated whenever the SQL Parser component encounters the WHERE keyword, and it is capable of handling any type of condition.
• Join: Join components are generated for every type of join supported by Oracle, and they can handle any joining condition specified in the SQL statement.
• Set: Set components are generated for all types of set operations - such as UNION, INTERSECT, and EXCEPT - when they are present in the SQL statement.

To generate a data flow using SQL statements:

1. Follow the instructions in Create a Data Flow to create a new data flow.
2. In the Data Flow Editor, click the icon.
3. On the Generate Data Flow From SQL page, insert your SQL statement in the Enter SQL Query window. See Example.
4. Click Generate.

   The SQL Parser feature generates the data flow, and displays a preview.

5. Click Apply to load the data flow on to the Design Canvas.
6. Save the data flow.
7. Drag and drop more elements on to the Design Canvas, if required.
8. Save and execute the data flow.

Note:

You cannot use the SQL parser feature to add to an existing data flow. A data flow added using the SQL parser will overwrite any existing data flow that is on the Design Canvas.

Example

The following example shows the use of an aggregation component in a SQL statement to generate a data flow:

create table DEMO_TARGET.SUB_QUANTITY
(
  QUANTITY_SOLD NUMBER(10,2),
PROD_SUBCATEGORY VARCHAR2(50 CHAR)
)

INSERT 
  /*+  APPEND PARALLEL  */ 
  INTO DEMO_TARGET.SUB_QUANTITY
  (
    QUANTITY_SOLD ,
    PROD_SUBCATEGORY 
  ) 
SELECT 
  (SUM(SALES.QUANTITY_SOLD)) ,
  PRODUCTS.PROD_SUBCATEGORY  
FROM 
  SH.SALES SALES  INNER JOIN  SH.PRODUCTS PRODUCTS  
    ON  SALES.PROD_ID=PRODUCTS.PROD_ID
GROUP BY
  PRODUCTS.PROD_SUBCATEGORY  

The generated data flow will look like this: