Re: A stored query?

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 Received on Thu Aug 30 2007 - 09:41:23 CDT