Re: A stored query?

On Aug 30, 10:18 am, Anthony Smith <mrsmi..._at_hotmail.com> wrote:
> On Aug 30, 9:41 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
>
>
>
>
> > On Aug 30, 9:16 am, Anthony Smith <mrsmi..._at_hotmail.com> wrote:
>
> > > I have this query in which I am trying to figure out the best way to
> > > get a result set for my application.
> > > I really want to be able to call a query, function, etc and pass it
> > > three values and get a result set back. Before I was using views to
> > > simplify my sql statements because I am using a ORM framework to
> > > interface with the db.
>
> > > I think I am having a hard time putting this into a view or trying to
> > > grasp the best way to do this. is there a such thing as a stored
> > > query? I don't think my ORM will let me call Oracle functions.
>
> > > select origin, sum(revenue), trans_dt from revenue
> > > where
> > > origin in(
> > > select ORIGIN from SPLIT_ORIGIN_ASSIGNMENT
> > > where EFFECTIVE_DT <= '31-AUG-07' and EXPIRATION_DT >= '31-AUG-07')
> > > AND kiac_acct_nbr not in
> > > (select distinct KIAC_ACCT_NBR from aff_kiac_acct_alignment where
> > > EFFECTIVE_DT <= '31-AUG-07' and EXPIRATION_DT >= '31-AUG-07')
> > > AND
> > > GET_QTR_BY_DATE(TRANS_DT) = 1 and
> > > GET_YEAR_BY_DATE(TRANS_DT) = 2007
> > > group by
> > > origin, trans_dt
>
> > The issue is the un-aliased aggregate in the select list. This should
> > work:
>
> > create or replace view revene_by_origin as
> > select origin, sum(revenue) rev_sum, trans_dt from revenue
> > where
> > origin in(
> > select ORIGIN from SPLIT_ORIGIN_ASSIGNMENT
> > where EFFECTIVE_DT <= '31-AUG-07' and EXPIRATION_DT >= '31-AUG-07')
> > AND kiac_acct_nbr not in
> > (select distinct KIAC_ACCT_NBR from aff_kiac_acct_alignment where
> > EFFECTIVE_DT <= '31-AUG-07' and EXPIRATION_DT >= '31-AUG-07')
> > AND
> > GET_QTR_BY_DATE(TRANS_DT) = 1 and
> > GET_YEAR_BY_DATE(TRANS_DT) = 2007
> > group by
> > origin, trans_dt;
>
> > Change view name or column alias as you see fit.
>
> > David Fitzjarrell
>
> David,
>
> Many thanks, this is exactly what I needed. Now how can I have this
> setup in a way where I can query it passing those hard-coded values
> (1,2007, 31-AUG-07) instead of having them hard-coded?- Hide quoted text -
>
> - Show quoted text -

Not to my knowledge. To have those as dynamic parameters you'll need a function/stored procedure. A pipelined function might be worth examining; visit

http/www.psoug.org/library.html

and search for pipelined functions.

David Fitzjarrell Received on Thu Aug 30 2007 - 11:27:09 CDT