Result Set Apply Function with Input SQL and Output a Result Set

This function applies a model where the input is one or more SQL queries and the output is a result set.

For general information about result set apply functions, see Result Set Apply Functions.

Syntax

FUNCTION apply_function_name_SR(
      case_table_sql    IN VARCHAR2,
      additional_sql_1  IN VARCHAR2,
      additional_sql_n  IN VARCHAR2,
      model_name        IN VARCHAR2 DEFAULT activity_default,
      feature_table     IN VARCHAR2 DEFAULT activity_default,
      RETURN apply_result_set;

Parameters and Defaults

The procedure has one kind of default:

Table 1 Apply Function with Input SQL and Output Result Set Parameters

Parameter Description
apply_function_name_SR The name specified for the function when the code was generated with the _SR suffix added.
case_table_sql A SQL query that returns valid case data for the model being applied.
additional_sql_1 The first SQL query that returns valid additional data. This parameter is optional.
additional_sql_n The n-th SQL query that returns valid additional data. This parameter is optional.
model_name The name of the model being applied.
feature_table Feature ID to text mapping table generated by the text transform. Can be used to translate model detail output containing columns affected by a text transformation.
result_set The result set, a TYPE object, as described above.

Usage Notes

This procedure includes all necessary data preparation, data transformations, and apply settings. The procedure creates a result set.

You must provide at least one query that returns valid case table data for the model being applied.

You can supply a feature_table name different from the default name, but you must supply the same name in both Build and Apply procedures.

Examples

These examples use the tables and views used by the Oracle Data Mining sample programs.

  1. Find all positive predictions for customers who are strictly more than 25 years old, and sort the predictions so that highest probability is first:
    SELECT s.dmr$case_id, s.prediction, s.probability FROM
      (
        SELECT MYAPPLY('SELECT * FROM SH.CUSTOMERS WHERE AGE > 25 ', 
                       'SELECT * FROM SH.SUPPLEMENTARY_DEMOGRAPHICS',
                       'SELECT * FROM SH.SALES') as pset FROM dual
      ) t, TABLE(t.pset) s
    WHERE s.prediction = 1
    ORDER BY s.probability DESC
    
  2. Create all of the input data (both an ordinary relational table and a transactional table) on the fly and score against the created data.
     select s.dmr$case_id, s.prediction, s.probability from
      (
        select MYAPPLY(
          case_table_sql => 
            'SELECT 
              2 as "CUST_ID",
              52778 as "COUNTRY_ID",
              52286 as "CUST_CITY_ID",
              10000 "CUST_CREDIT_LIMIT",
              ''F'' "CUST_GENDER",
              ''I: 170,000 - 189,999'' as "CUST_INCOME_LEVEL",
              NULL as "CUST_MARITAL_STATUS",
              52733 as "CUST_STATE_PROVINCE_ID",
              ''A'' as "CUST_VALID",
              1957 as "CUST_YEAR_OF_BIRTH"   
            FROM dual',
          additional_sql_2 =>
            '(SELECT 2 as cust_id, 13 as prod_id, 1 as quantity_sold, 1205.99 as amount_sold 
              FROM dual
            UNION ALL
            SELECT 2 as cust_id, 13 as prod_id, 1 as quantity_sold, 1232.16 as amount_sold 
             FROM dual
            UNION ALL
            SELECT 2 as cust_id, 14 as prod_id, 1 as quantity_sold, 1259.99 as amount_sold 
             FROM dual
            UNION ALL
            SELECT 2 as cust_id, 14 as prod_id, 1 as quantity_sold, 1259.99 as amount_sold 
             FROM dual)'
        ) as pset from dual
      ) t, TABLE(t.pset) s