Home » SQL & PL/SQL » SQL & PL/SQL » query
query [message #423412] Thu, 24 September 2009 01:31 Go to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
How can we get the product of the field in a table having multiple rows without using the loop.

for example:
I have this field in table test
a
100
200
300

then result should come -100*200*300= 6000000

i want to know as i was asked this ques in an interview.
Re: query [message #423413 is a reply to message #423412] Thu, 24 September 2009 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There are many answers.
What did you try, search, find?

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code , use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Thu, 24 September 2009 01:34]

Report message to a moderator

Re: query [message #423432 is a reply to message #423413] Thu, 24 September 2009 03:19 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
I have tried searching on the net but cant find it.

Test Case:

create table test_product(
id number(2),
test_no number);

insert into test_product values( 1, 20);
insert into test_product values( 2, 10);
insert into test_product values( 3, 40);
insert into test_product values( 4, 10);

Expected result: product of test_no i.e -80000 from a single select query.


Re: query [message #423434 is a reply to message #423432] Thu, 24 September 2009 03:34 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is a interesting case. What came to my mind first, was Tom Kyte's user defined STRAGG aggregate function.

I have adapted that to generate a "product" aggregate function:

--- Create Aggregate Type
CREATE OR REPLACE TYPE product_agg_type
 AS OBJECT
  (
       total number,

       STATIC FUNCTION
            ODCIAggregateInitialize(sctx IN OUT product_agg_type )
            RETURN NUMBER,

       MEMBER FUNCTION
           ODCIAggregateIterate(self IN OUT product_agg_type ,
                                value IN number )
           RETURN NUMBER,

      MEMBER FUNCTION
           ODCIAggregateTerminate(self IN product_agg_type,
                                  returnValue OUT  number,
                                  flags IN number)
           RETURN NUMBER,

      MEMBER FUNCTION
           ODCIAggregateMerge(self IN OUT product_agg_type,
                              ctx2 IN product_agg_type)
           RETURN NUMBER
   );
/

--- Create Aggregate Type Body
CREATE OR REPLACE TYPE BODY product_agg_type
    IS

    STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT product_agg_type)
      RETURN NUMBER
    IS
    BEGIN
        sctx := product_agg_type( 1 );
        RETURN ODCIConst.Success;
   END;

   MEMBER FUNCTION ODCIAggregateIterate(self IN OUT product_agg_type,
                                        value IN number )
     RETURN NUMBER
   IS
   BEGIN
       self.total := self.total * value;
       RETURN ODCIConst.Success;
   END;

   MEMBER FUNCTION ODCIAggregateTerminate(self IN product_agg_type,
                                          returnValue OUT number,
                                          flags IN number)
     RETURN NUMBER
   IS
   BEGIN
       returnValue := self.total;
       return ODCIConst.Success;
   END;

   MEMBER FUNCTION ODCIAggregateMerge(self IN OUT product_agg_type,
                                      ctx2 IN product_agg_type)
     RETURN NUMBER
   IS
   BEGIN
       self.total := self.total * ctx2.total;
       return ODCIConst.Success;
   END;

   end;
/

-- Create aggregate function
CREATE OR REPLACE FUNCTION product(input number)
      RETURN number
    PARALLEL_ENABLE AGGREGATE USING product_agg_type;
/


Then you can use "product" like any other aggregate function:

SQL> create table test_product(
  2  id number(2),
  3  test_no number);

Table created.

SQL>
SQL> insert into test_product values( 1, 20);

1 row created.

SQL> insert into test_product values( 1, 30);

1 row created.

SQL> insert into test_product values( 2, 10);

1 row created.

SQL> insert into test_product values( 3, 40);

1 row created.

SQL> insert into test_product values( 3, 40);

1 row created.

SQL> insert into test_product values( 4, 10);

1 row created.

SQL>
SQL> SELECT product(test_no) FROM test_product;

PRODUCT(TEST_NO)
----------------
        96000000

SQL>
SQL> SELECT id, product(test_no) FROM test_product GROUP BY id;

        ID PRODUCT(TEST_NO)
---------- ----------------
         1              600
         2               10
         3             1600
         4               10

SQL>
Re: query [message #423435 is a reply to message #423434] Thu, 24 September 2009 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is one way, there are others that do not require any object creation, just using what exist yet.

Regards
Michel
Re: query [message #423436 is a reply to message #423432] Thu, 24 September 2009 03:43 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Another Way
Re: query [message #423438 is a reply to message #423434] Thu, 24 September 2009 03:49 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
thanks Thomas, but the answer which I am looking for is something like we do for the summation of fields in a row by using cube/rollup.

Re: query [message #423441 is a reply to message #423438] Thu, 24 September 2009 03:55 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, when you are not able ask for what you really want in the first place, then you have to take what you get.
Re: query [message #423442 is a reply to message #423412] Thu, 24 September 2009 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table test_product (
  2   id number(2),
  3   test_no number);

Table created.

SQL> insert into test_product values (1, 20);

1 row created.

SQL> insert into test_product values (2, 10);

1 row created.

SQL> insert into test_product values (3, 40);

1 row created.

SQL> insert into test_product values (4, 10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test_product order by 1;
        ID    TEST_NO
---------- ----------
         1         20
         2         10
         3         40
         4         10

4 rows selected.

SQL> select round(exp(sum(ln(test_no)))) prod from test_product;
      PROD
----------
     80000

1 row selected.

SQL> col prod new_value prod
SQL> select substr(max(sys_connect_by_path(test_no,'*')),2) prod
  2  from test_product
  3  connect by prior id = id - 1
  4  start with id = 1
  5  /
PROD
-----------
20*10*40*10

1 row selected.

SQL> select &prod prod from dual
  2  /
      PROD
----------
     80000

1 row selected.

SQL> select prod
  2  from ( select id, val prod
  3         from test_product
  4         model
  5           dimension by (id)
  6           measures (test_no, 1 val)
  7           rules 
  8             ( val[any] order by id = nvl(val[cv()-1],1) * test_no[cv()] )
  9         order by id desc
 10       )
 11  where rownum = 1
 12  /
      PROD
----------
     80000

1 row selected.

Regards
Michel

[Updated on: Thu, 24 September 2009 03:57]

Report message to a moderator

Re: query [message #423444 is a reply to message #423438] Thu, 24 September 2009 04:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Aha! A chance to use my degree!!!

You want Logarithms. You can use Logarithms and simple addition to perform multiplication:

Log(100*200*300) = Log(100) + Log(200) + Log(300)

You can implement this in Oracle like this:
create table test_071 (col_1 number);

insert into test_071 values (100);
insert into test_071 values (200);
insert into test_071 values (300);

select power(10,sum(log(10,col_1))) from test_071;


[Drat - beaten to it]

[Updated on: Thu, 24 September 2009 04:05]

Report message to a moderator

Re: query [message #423447 is a reply to message #423444] Thu, 24 September 2009 04:42 Go to previous messageGo to next message
shaz
Messages: 182
Registered: June 2009
Senior Member
/forum/fa/3958/0/ /forum/fa/3958/0/ /forum/fa/3958/0/
thanks to all the gurus...

Re: query [message #423470 is a reply to message #423447] Thu, 24 September 2009 09:36 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Please explain why all of a sudden you seem to be over-excited, while you dismissed ThomasG's reply as not useful.
Re: query [message #423490 is a reply to message #423470] Thu, 24 September 2009 11:27 Go to previous message
Michel Cadot
Messages: 68704
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
We didn't (all) dismiss it and didn't (all) say it is not useful. It is just one way among several (as I said).
It is useful to show the usage of custom aggregate function but in the wrong place to show its usefulness. I also had to support such dismissal each time I posted a custom aggregate function (but once in Metalink forum many years ago) including the same "prod" function, maybe because the code is quite hard to read, understand how it works and needs some effort.

Regards
Michel
Previous Topic: UTL_FILE invalid directory problem
Next Topic: how to get the user who locked a record
Goto Forum:
  


Current Time: Mon Nov 04 05:26:21 CST 2024