Pattern Matching Over Rows

Setup

Run the code on this page in SQL*Plus to create the sample tables, data, etc. used by the examples in this section.

Be sure to read Using SQL Snippets ™ before executing any of these setup steps.

create table stock_prices
( stock_symbol varchar2(10) not null,
  day          integer      not null,  -- see Note 1
  price        number(4)
);

-- Note 1: for simplicity we'll store trade dates as integers, e.g. day 1, day 2, etc.

insert into stock_prices values ( 'W1', 1, 10 );
insert into stock_prices values ( 'W1', 2, 9  );
insert into stock_prices values ( 'W1', 3, 8  );
insert into stock_prices values ( 'W1', 4, 7  );
insert into stock_prices values ( 'W1', 5, 8  );
insert into stock_prices values ( 'W1', 6, 9  );
insert into stock_prices values ( 'W1', 7, 8  );
insert into stock_prices values ( 'W1', 8, 7  );
insert into stock_prices values ( 'W1', 9, 8  );
insert into stock_prices values ( 'W1',10, 9  );
insert into stock_prices values ( 'W1',11, 10 );

insert into stock_prices values ( 'W2', 1, 9  );
insert into stock_prices values ( 'W2', 2, 9  );
insert into stock_prices values ( 'W2', 3, 9  );
insert into stock_prices values ( 'W2', 4, 7  );
insert into stock_prices values ( 'W2', 5, 8  );
insert into stock_prices values ( 'W2', 6, 8  );
insert into stock_prices values ( 'W2', 7, 8  );
insert into stock_prices values ( 'W2', 8, 5  );
insert into stock_prices values ( 'W2', 9, 9  );
insert into stock_prices values ( 'W2',10, 9  );
insert into stock_prices values ( 'W2',11, 9  );

insert into stock_prices values ( 'V1', 1, 10 );
insert into stock_prices values ( 'V1', 2, 9  );
insert into stock_prices values ( 'V1', 3, 8  );
insert into stock_prices values ( 'V1', 4, 7  );
insert into stock_prices values ( 'V1', 5, 6  );
insert into stock_prices values ( 'V1', 6, 5  );
insert into stock_prices values ( 'V1', 7, 6  );
insert into stock_prices values ( 'V1', 8, 7  );
insert into stock_prices values ( 'V1', 9, 8  );
insert into stock_prices values ( 'V1',10, 9  );
insert into stock_prices values ( 'V1',11, 10 );

insert into stock_prices values ( 'V2', 1, 9  );
insert into stock_prices values ( 'V2', 2, 9  );
insert into stock_prices values ( 'V2', 3, 8  );
insert into stock_prices values ( 'V2', 4, 8  );
insert into stock_prices values ( 'V2', 5, 6  );
insert into stock_prices values ( 'V2', 6, 6  );
insert into stock_prices values ( 'V2', 7, 7  );
insert into stock_prices values ( 'V2', 8, 7  );
insert into stock_prices values ( 'V2', 9, 8  );
insert into stock_prices values ( 'V2',10, 8  );
insert into stock_prices values ( 'V2',11, 9  );

insert into stock_prices values ( 'V3', 1, 5  );
insert into stock_prices values ( 'V3', 2, 5  );
insert into stock_prices values ( 'V3', 3, 5  );
insert into stock_prices values ( 'V3', 4, 7  );
insert into stock_prices values ( 'V3', 5, 7  );
insert into stock_prices values ( 'V3', 6,10  );
insert into stock_prices values ( 'V3', 7,10  );
insert into stock_prices values ( 'V3', 8, 9  );
insert into stock_prices values ( 'V3', 9, 9  );
insert into stock_prices values ( 'V3',10, 8  );
insert into stock_prices values ( 'V3',11, 6  );

insert into stock_prices values ( 'FLAT', 1, 7 );
insert into stock_prices values ( 'FLAT', 2, 7 );
insert into stock_prices values ( 'FLAT', 3, 7 );
insert into stock_prices values ( 'FLAT', 4, 7 );
insert into stock_prices values ( 'FLAT', 5, 7 );
insert into stock_prices values ( 'FLAT', 6, 7 );
insert into stock_prices values ( 'FLAT', 7, 7 );
insert into stock_prices values ( 'FLAT', 8, 7 );
insert into stock_prices values ( 'FLAT', 9, 7 );
insert into stock_prices values ( 'FLAT',10, 7 );
insert into stock_prices values ( 'FLAT',11, 7 );

commit;
 



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-12468.html]SQL Snippets: Pattern Matching Over Rows - Setup[/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-12468.html">SQL Snippets: Pattern Matching Over Rows - Setup</a>

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

  • Link Text : SQL Snippets: Pattern Matching Over Rows - Setup
  • URL (href): http://www.sqlsnippets.com/en/topic-12468.html