Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Xavier.

Asked: January 12, 2018 - 2:02 pm UTC

Last updated: January 15, 2018 - 9:53 am UTC

Version: 11G Release 11.2.0.3.0

Viewed 1000+ times

You Asked

Hello Tom.

I've to translate a mathematical formula from "Google Sheet" to a "trigger in Oracle".

A part of this formula in Google sheet is MOYENNE(3;1;MAX(1;1;9;1);MAX(1;1;9;1))

I know translate this formula like that :

SELECT MAX(foo."nb") FROM (SELECT 1 AS "nb" FROM dual UNION
SELECT 1 FROM dual UNION
SELECT 9 FROM dual UNION
SELECT 1 FROM dual)foo;


I think it's not the better translation.

Have you got an idea for upgrade my translation ?

Thank you.

and Chris said...

So, plugging that into Google Sheets gives:

5.5

So what's it doing?

Taking the mean of:

3
1
max(1, 1, 9, 1) = 9
max(1, 1, 9, 1) = 9

In SQL this could look something like:

with mx as (
  select max( nb ) n
  from (
    select 1 as nb from dual
    union
    select 1 from dual
    union
    select 9 from dual
    union
    select 1 from dual
  )
), vals as ( 
  select 3 n from dual union all
  select 1 n from dual union all
  select n from mx union all
  select n from mx 
)
  select avg(n) from vals;

AVG(N)   
     5.5

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PL/SQL

Racer I., January 15, 2018 - 8:48 am UTC

Hi,

Since the requirement mentioned a trigger : assume the values are available in the current row or in constants. Also assume the formula (MOYENNE is apparently french for average) is fixed :

declare
  result NUMBER;
begin
  dbms_output.enable;
  result := (3 + 1 + GREATEST(1, 1, 9, 1) + GREATEST(1, 1, 9, 1)) / 4;
  dbms_output.put_line(result);
end;


Apparently Oracle doesn't have an AVG(x, y, ...) PL/SQL-function?

regards,
Connor McDonald
January 15, 2018 - 9:34 am UTC

nice input.

And yes, AVG is not overloaded to take an arbitrary number of parameters, but you could build your own

SQL> create or replace
  2  type avg_extended as object
  3   (
  4      elems sys.odcinumberlist,
  5
  6      MEMBER FUNCTION    result return number,
  7
  8    CONSTRUCTOR FUNCTION avg_extended(self in out nocopy avg_extended,
  9       p_elems sys.odcinumberlist
 10       ) return self as result
 11
 12    )
 13  /

Type created.

SQL> sho err
No errors.
SQL>
SQL> create or replace
  2  type body avg_extended is
  3
  4     MEMBER FUNCTION result RETURN number IS
  5        tot number := 0;
  6     BEGIN
  7        FOR i IN 1 .. elems.count LOOP
  8              tot   := tot + elems(i);
  9        END LOOP;
 10        RETURN tot/elems.count;
 11     END;
 12
 13    CONSTRUCTOR FUNCTION avg_extended(self in out nocopy avg_extended,
 14       p_elems sys.odcinumberlist
 15       ) return self as result is
 16    begin
 17      elems := p_elems;
 18      return;
 19    end;
 20
 21  end;
 22  /

Type body created.

SQL>
SQL> set serverout on
SQL> declare
  2    x avg_extended := avg_extended(p_elems=>sys.odcinumberlist(1,3,5,6,2,3,5,16));
  3  begin
  4    dbms_output.put_line(x.result);
  5  end;
  6  /
5.125

PL/SQL procedure successfully completed.

SQL>
SQL>


A reader, January 15, 2018 - 9:35 am UTC

It's perfect thnak you very much for your help !
Connor McDonald
January 15, 2018 - 9:53 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.