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.
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;
The procedure has one kind of default:
activity_default
: The default is the value specified in the activity from which code was generated. For example, model_name
is by default the name specified in the activity.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. |
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.
These examples use the tables and views used by the Oracle Data Mining sample programs.
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
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
Copyright © 2006, 2008, Oracle. All rights reserved.