Re: Stuck: Parameter Views and/or DBMS_SQL

It would help if you gave the version of Oracle you are using since the possibilities vary with release.

However, the change in performance when using a function to return the begindate/endate is probably due to the fact that the functions are called for every row tested. The standard trick to avoid this is to use as driving subquery of the form

    (select function from dual)

so that
>daterange between :BEGINDATE and :ENDDATE
become

      daterange >= (select get_begin_date() from dual) and daterange <= (select get_end_date() from dual)

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Oracle Nospam wrote in message <38d1ac51.31588245_at_news.pdnt.com>...
>I basically have two tables with information I need to merge. Both
>tables have common fields to link (a, b, c), and each row has a date
>field (not shown here for simplicity):
>
>(Group1 Table)
>a b c d
>---- ---- ---- ----
>25 01 0001 5
>25 01 0001 10
>25 02 0002 4
>etc.
>
>(Group2 Table)
>a b c e
>---- ---- ---- ----
>25 01 0001 20
>25 02 0002 6
>25 02 0002 34
>etc.
>
>The result should be:
>a b c sum(d) sum(e)
>---- ---- ---- ------ ------
>25 01 0001 15 20
>25 02 0002 4 40
>
>My SQL is:
>
>SELECT G1.*, G2.g2sum FROM (SELECT a, b, c, sum(d) from Group1 WHERE
>daterange between :BEGINDATE and :ENDDATE GROUP BY a, b, c) g1,
>(SELECT a, b, c, sum(e) "g2sum" from Group2 WHERE daterange between
>:BEGINDATE and :ENDDATE GROUP BY a, b, c) g2 WHERE G1.a = G2.a AND
>G1.b = G2.b AND G1.c = G2.c;
>
>I can run this in PL/SQL, and it works perfectly. However, I cannot
>create a view from this because of the parameters :BEGINDATE and
>:ENDDATE. (If I were to hardcode the dates, it works exactly how I
>want it.) I created a function to set a parameter, and used that
>(i.e. begindate.get_parm), but that caused the view to run 10x as
>long!
>
>Therefore, I tried to use DBMS_SQL in a procedure and pass the
>begindate and enddate to the procedure. It works fine; however, I
>need for the procedure to return the results of the query (just like
>the view did). When I run the procedure, it just says PL/SQL complete
>(or something like that). Can DBMS_SQL create a result set that an
>external report writer like Crystal Reports can read and format the
>data?
>
>Please post replies to group. Thanks in advance for your help.
Received on Fri Mar 17 2000 - 16:39:28 CST