Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: A stored query?

Re: A stored query?

From: Anthony Smith <mrsmithq_at_hotmail.com>
Date: Thu, 30 Aug 2007 08:18:11 -0700
Message-ID: <1188487091.588913.34520@57g2000hsv.googlegroups.com>


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? Received on Thu Aug 30 2007 - 10:18:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US