Re: need help w complex sql query

Don't know if this helps you or not, but why not use a table function instead of, or in addition to a view?

Being more of a C++ developer than an SQL developer, whenever I see something like this, I tend to think in terms of wanting to pass parameters (contract value, perhaps a date range) to a function that can be reused, and become very frustrated with the lack of a way to do parameterized views in Oracle.

As such, I tend to favor the use of pipelined table functions, and off hand, I'd probably approach it like so

  create function eom_prices(contract varchar2)

     return prices%rowtype pipelined
  ...

Not shown here of course is the PL/SQL logic to select the rows from the prices table for a particular contract value on the days of interest.
I believe you already know how to do that with PL/SQL.

For convenience, you could create a set of views like

  create view spa_prices as
  select * from table( eom_prices('SPA') );

but depending on your circumstances, you may not need the views at all.

Another approach would be to just make a function that returned the eom dates, and then use that in your original view

 create view v as
 SELECT TDATE, PRICE FROM X.PRICES
 WHERE CONTRACT = 'SPA' AND
 TDATE = in (select * from table (eom_dates))  ORDER BY TDATE; Mike Stenzler <external_at_rxr.com> wrote in news:3F674163.7CA77C71_at_rxr.com:

> I'm trying to create a view on a large table of price data. The rows I
> want to belong to the view are of a specific financial instrument and
> should only be the last business day of the month. The target table has
> rows for every day, with weekends and holidays being skipped.
>
> I'm able to write a SQL query that returns only End Of Month days, but
> if the EOM falls on a weekend, that month returns nothing -
>
> SELECT TDATE, PRICE FROM X.PRICES
> WHERE CONTRACT = 'SPA' AND
> TDATE = LAST_DATE(TDATE)
> ORDER BY TDATE;
>
> A snippet of what this will return ...
> 31-DEC-02 879.82
> 31-JAN-03 855.7
> 28-FEB-03 841.15
> 31-MAR-03 848.18
> 30-APR-03 916.92
> 30-JUN-03 974.5
> 31-JUL-03 990.31
>
> Notice that there is no value returned for MAY or AUGUST as the last
> day of these months fall on the weekend so they don't exist in the
> target table. What is desired is for 30-MAY-03 and 29-AUG-03 to be
> returned instead as they are the last business days of the month.
>
> Any ideas as to how to re-write this query without procedural language
> as it must create a view?
>
> Mike
>
>
Received on Tue Sep 16 2003 - 14:02:14 CDT