RE: Re[2]: Instead of SUM() I require MULTIPLY

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Sun, 11 Dec 2005 17:21:51 +0100
Message-ID: <002a01c5fe6e$feb57650$6501a8c0@LAPTOPLEX>

yep, I agree -- MODEL is very powerful -- but at the same time, if you ask for my personal opinion, the Oracle MODEL syntax is not very intuitive and *far* from elegant. I would even dare to say that it is really ugly :-) and doesn't belong in a language like SQL.

but hey, it's there, so why not go ahead and use it. I personally prefer to extend SQL with user-defined PL/SQL functions (for the regular scalar functions that are missing) and with additional aggregate functions using the data cartridge stuff -- which started this thread. Keeps your SQL code clean and simple.

kind regards,

Lex.  

---

Jonathan Lewis Seminar http://www.naturaljoin.nl/events/seminars.html

---

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Anthony Molinaro
Sent: Sunday, December 11, 2005 15:48
To: jonathan_at_gennick.com; Oracle-L Freelists Subject: RE: Re[2]: Instead of SUM() I require MULTIPLY

Jonathan,
  Thanks for the plug :)

Additionally, if you're on 10g, you can use MODEL to create a running product which has no problems with negatives because logarithms are not used (since it uses plain multiplication, you still have to decide how to handle nulls and 0, but that's trivial and shouldn't hamper the readability of the solution).

For example:

create table dropme (id number);
  insert into dropme values (1);
  insert into dropme values (1);
  insert into dropme values (2);
  insert into dropme values (3);
  insert into dropme values (4);
  insert into dropme values (5);

 select id, tmp running_prod
   from dropme
   model
    dimension by(row_number()over(order by id) rn )     measures(id, 0 tmp)
    rules
   (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()]));

SQL> select id, tmp running_prod

  2     from dropme
  3     model
  4      dimension by(row_number()over(order by id) rn )
  5      measures(id, 0 tmp)
  6      rules
  7     (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()]));

        ID RUNNING_PROD
---------- ------------
         1            1
         1            1
         2            2
         3            6
         4           24
         5          120

6 rows selected.

SQL> update dropme set id = id*-1 where id in (1,3,5);

4 rows updated.

SQL> select id, tmp running_prod

  2     from dropme
  3     model
  4      dimension by(row_number()over(order by id) rn )
  5      measures(id, 0 tmp)
  6      rules
  7     (tmp[any] = nvl2(id[cv()-1],tmp[cv()-1]*id[cv()],id[cv()]));

        ID RUNNING_PROD
---------- ------------


        -5           -5

        -3           15

        -1          -15

        -1           15

         2           30
         4          120

6 rows selected.

No one seems to be using it much (yet!), but MODEL is very powerful and allows you to do so much with so little.
If you've done any PROLOG, you'd immediately see how cool MODEL can be.

Regards,
  Anthony

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Dec 11 2005 - 10:22:02 CST