Re: Stuck: Parameter Views and/or DBMS

From: <michael_bialik_at_my-deja.com>
Date: Fri, 17 Mar 2000 21:29:21 GMT
Message-ID: <8au83c$4eh$1@nnrp1.deja.com>

Hi.

I used the same approach and it works OK ( at least the EXPLAIN plans are identical ).

  CREATE TABLE TAB1 (
  A          NUMBER (4),
  B          NUMBER (4),
  C          NUMBER (4),
  DATERANGE  DATE,
  D          NUMBER (4))

TABLESPACE USER_TBS
   PCTFREE 10 PCTUSED 40
   INITRANS 1 MAXTRANS 255
STORAGE (
   INITIAL 56K NEXT 56K PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 121 )
   NOCACHE; CREATE INDEX TAB1_ABC ON
  TAB1(A, B, C)
  TABLESPACE USER_TBS PCTFREE 10
  STORAGE(INITIAL 56K NEXT 56K PCTINCREASE 0 ) ; CREATE INDEX TAB1_DT ON
  TAB1(DATERANGE)
  TABLESPACE USER_TBS PCTFREE 10
  STORAGE(INITIAL 56K NEXT 56K PCTINCREASE 0 ) ; CREATE TABLE TAB2 (

  A          NUMBER (4),
  B          NUMBER (4),
  C          NUMBER (4),
  DATERANGE  DATE,
  E          NUMBER (4))

TABLESPACE USER_TBS
   PCTFREE 10 PCTUSED 40
   INITRANS 1 MAXTRANS 255
STORAGE (
   INITIAL 56K NEXT 56K PCTINCREASE 0
   MINEXTENTS 1 MAXEXTENTS 121 )
   NOCACHE; CREATE INDEX TAB2_ABC ON
  TAB2(A, B, C)
  TABLESPACE USER_TBS PCTFREE 10
  STORAGE(INITIAL 56K NEXT 56K PCTINCREASE 0 ) ; CREATE INDEX TAB2_DT ON
  TAB2(DATERANGE)
  TABLESPACE USER_TBS PCTFREE 10
  STORAGE(INITIAL 56K NEXT 56K PCTINCREASE 0 ) ; PACKAGE parm_pck IS

Initialize 2 package variables PROCEDURE set_range( p_startdate IN DATE, p_enddate IN DATE );
Returns package variable FUNCTION get_begindate RETURN DATE; PRAGMA RESTRICT_REFERENCES ( get_begindate, WNDS, WNPS, RNDS );
Returns package variable FUNCTION get_enddate RETURN DATE; PRAGMA RESTRICT_REFERENCES ( get_enddate, WNDS, WNPS, RNDS ); END parm_pck;

CREATE OR REPLACE VIEW VVV ( A,
B, C, SUM_E, SUM_D
) AS SELECT t1.a, t1.b, t1.c, t1.sum_d, t2.sum_e FROM ( SELECT a,b,c,sum(d) sum_d FROM tab1
WHERE daterange between parm_pck.get_begindate and parm_pck.get_enddate

GROUP BY a,b,c ) t1,
( SELECT a,b,c,sum(e) sum_e FROM tab2 WHERE daterange between parm_pck.get_begindate and parm_pck.get_enddate

GROUP BY a,b,c ) t2
WHERE t1.a = t2.a and t1.b= t2.b and t1.c = t2.c

PL/SQL code :

parm_pck.set_range( your_startdate, your_enddate );

Now you can make
SELECT * FROM vvv;

HTH. Michael.

In article <38d1ac51.31588245_at_news.pdnt.com>, nospam_at_nospam.com (Oracle Nospam) wrote:
> 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.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 17 2000 - 15:29:21 CST

Re: Stuck: Parameter Views and/or DBMS_SQL