MODEL Clause

RETURN ROWS

In this tutorial we explore the RETURN ROWS component of the MODEL clause. With it you can control whether all rows selected or only those rows updated by model rules are returned by the query.

RETURN ALL ROWS

RETURN ALL ROWS is the default behaviour for MODEL queries. It causes the query to returns all rows selected whether they were updated by a rule or not.

break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN ALL ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
 
   KEY ROW_STATUS
------ ----------
     1 UPDATED
     2 x
     3 UPDATED
     4 x
     5 UPDATED
     6 x
     7 UPDATED
     8 x
     9 x
    10 x
 

RETURN UPDATED ROWS

RETURN UPDATED ROWS causes the query to return only those rows updated by a rule.

break on group_1 skip 1 duplicates

select key, row_status
from   t
model
  RETURN UPDATED ROWS
  dimension by ( key )
  measures     ( cast( 'x' as varchar2(10) ) as row_status )
  rules        ( row_status[key in (1,3,5,7)] = 'UPDATED' )
order by key ;
 
   KEY ROW_STATUS
------ ----------
     1 UPDATED
     3 UPDATED
     5 UPDATED
     7 UPDATED
 



Linking to SQL Snippets ™

To link to this page in Oracle Technology Network Forums or OraFAQ Forums cut and paste this code.

  • [url=http://www.sqlsnippets.com/en/topic-12299.html]SQL Snippets: MODEL Clause - RETURN ROWS[/url]

To link to this page in HTML documents or Blogger comments cut and paste this code.

  • <a href="http://www.sqlsnippets.com/en/topic-12299.html">SQL Snippets: MODEL Clause - RETURN ROWS</a>

To link to this page in other web sites use the following values.

  • Link Text : SQL Snippets: MODEL Clause - RETURN ROWS
  • URL (href): http://www.sqlsnippets.com/en/topic-12299.html