Stuck: Parameter Views and/or DBMS_SQL

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 Thu Mar 16 2000 - 22:18:36 CST