Solved

# Replicate Excel Math Function in Oracle SQL or PL/SQL code

Posted on 2006-11-27
Medium Priority
826 Views
I need to replicate an Excel math function as shown below in Oracle sql or PL/SQL stored procedure/function;

EXAMPLE 1:

Input: SUM(A1:B2)

Output: SUM(A1 + B1 + A2 + B2)

EXAMPLE 2:

Input: SUM(A1:A9)

Output: SUM(A1 + A2 + A3 + A4 + A5 + A6 + A7 + A8 + A9)

Thank you
0
Question by:RND2006
• 8
• 4
• 3
• +1

LVL 35

Expert Comment

ID: 18020315
Do you want this as a database procedure, or do you just need this in some kind of user interface screen, like: Oracle Forms, JDeveloper, etc.?
0

Author Comment

ID: 18020417
Oracle database procedure.

Thank you very much.
0

LVL 78

Expert Comment

ID: 18020418
I'm not sure this even makes sense in the relational world.  This query would need to be based on the exact layout of the columns in the table.  It would also have to look at the data type of the column so it can ignore all non-numeric columns (as Excel does).  Then if you ever needed to add another column to the table, you would probably have to either drop and re-create the table so the new column is in the right place or create a view based on the base table to order the columns properly.

I have no doubt that we can come up with something pretty darn close but it would take A LOT of code to get anything even close to usable.

0

Author Comment

ID: 18020521
I do agree with you but I will try to explain the requirement to you as it was given to me;

The requirement is to just display the cells that sum up to a given input (They require the cells that adds up to the
sum as given).

For example: if the input is A1 and A3 for "SUM(A1:A3)".
The result should display the cells that constitute to the sum. i.e. A1, A2, A3

Another example is, if the input is say, A1 and C3;
The result should display;
A1, A2, A3, B1, B2, B3, C1, C2, C3

0

Author Comment

ID: 18020634
In addition to my previous explanation I am adding few more lines to explain as much as possible;

There will be only two input parameters to the procedure;

For example: A1 and B3

The result should display (please understand that this has
to be based on the Excel math function SUM(A1:B3));

Result should display: A1, A2, A3, B1, B2, B3

Thank you.
0

Author Comment

ID: 18020762
Any thoughts?

Thank you
0

LVL 35

Expert Comment

ID: 18020979
The first problem is that an Oracle database does not have cells like an Excel spreadsheet does.  Yes, Oracle tables do have columns, and there may be rows (records) of data in the table, but they are not in any particular order, unless you add an explicit "order by" clause to whatever query or procedure you use.

This seems like a very unusual request for Oracle, or an attempt to do something that Oracle is not designed for.  Oracle is a database, not a spreadsheet, and Oracle database procedures operate in the database (on the server) not in client or data presentation tools (like Excel).

Which tool do you plan to use the present this data from Oracle to the operator?
0

Author Comment

ID: 18021027
Is there a way to call/create an object and call excel application
there by use the excel functions within the pl/sql code?

Am I sounding right?
0

LVL 32

Expert Comment

ID: 18021406
The following assumes an input parameter of the format 'A1:B3' -

create or replace function list_cells(range_in in varchar2)
return varchar2 is

v_ltrs     varchar2(5);
v_nums     varchar2(11);
v_ltrs1    varchar2(2);
v_ltrs2    varchar2(2);
v_nums1     varchar2(5);
v_nums2     varchar2(5);
v_beg_ltr_val  number;
v_end_ltr_val  number;
v_beg_num_val  number;
v_end_num_val  number;
ret_list   varchar2(255) := null;

begin
v_ltrs := translate(upper(range_in),'#0123456789','#');
v_nums := translate(upper(range_in),'#ABCEDFGHIJKLMNOPQRSTUVWXYZ','#');

v_ltrs1 := substr(v_ltrs,1,instr(v_ltrs,':') - 1);
v_ltrs2 := substr(v_ltrs,instr(v_ltrs,':') + 1);
v_nums1 := substr(v_nums,1,instr(v_nums,':') - 1);
v_nums2 := substr(v_nums,instr(v_nums,':') + 1);
v_beg_ltr_val := ascii(v_ltrs1);
v_end_ltr_val := ascii(v_ltrs2);
v_beg_num_val := to_number(v_nums1);
v_end_num_val := to_number(v_nums2);

for l in v_beg_ltr_val..v_end_ltr_val
loop
for n in v_beg_num_val..v_end_num_val
loop
ret_list := ret_list||chr(l)||to_char(n)||'+';
end loop;
end loop;

return rtrim(ret_list,'+');
end;
/

example:
SQL> select list_cells('a2:d7') from dual;

LIST_CELLS('A2:D7')
--------------------------------------------------------------------------
A2+A3+A4+A5+A6+A7+B2+B3+B4+B5+B6+B7+C2+C3+C4+C5+C6+C7+D2+D3+D4+D5+D6+D7
0

LVL 35

Expert Comment

ID: 18021576
Do you just need to create list of cells between two input parameters?  If so, this is possible in PL\SQL as awking00 suggested, but using a stored PL\SQL procedure in an Oracle database to do this certainly does not look like the easiest way to do this, nor the most efficient tool for the job.

Or, do you actually have to find and process the values in that list of cells also?

No, Oracle database procedures cannot call client applications like Excel.  An Oracle Forms application (which is a client or application-server based screen application) can call Excel, if the client or application server is running on Windows.
0

LVL 32

Expert Comment

ID: 18021579
I just realized that the above will only work for columns A through Z and needs to be modified to accommodate columns AA through IV. This complicates matters quite a bit. I will continue to work on it and get back to you.
0

Author Comment

ID: 18021885
You are such a wonderful person.

I was about to ask you and this is really a great relief to me.

Please kindly get back to me with a solution.

I think AA through IV is fine.

Thank you very very much.
0

LVL 32

Accepted Solution

awking00 earned 2000 total points
ID: 18022535
This was a little tricky but I think this will do it.

create or replace function list_cells(range_in in varchar2)
return varchar2 is

v_ltrs     varchar2(5);
v_nums     varchar2(11);
v_ltrs1    varchar2(2);
v_ltrs2    varchar2(2);
v_nums1     varchar2(5);
v_nums2     varchar2(5);
v_beg_num_val  number;
v_end_num_val  number;
ltr1a varchar2(1);
ltr1b varchar2(1);
ltr2a varchar2(1);
ltr2b varchar2(1);
start_no  pls_integer;
end_no    pls_integer;

ret_list   varchar2(255) := null;

begin
v_ltrs := translate(upper(range_in),'#0123456789','#');
v_nums := translate(upper(range_in),'#ABCEDFGHIJKLMNOPQRSTUVWXYZ','#');

v_ltrs1 := substr(v_ltrs,1,instr(v_ltrs,':') - 1);
v_ltrs2 := substr(v_ltrs,instr(v_ltrs,':') + 1);
v_nums1 := substr(v_nums,1,instr(v_nums,':') - 1);
v_nums2 := substr(v_nums,instr(v_nums,':') + 1);

if length(v_ltrs1) = 2 then
ltr1a := substr(v_ltrs1,1,1);
ltr1b := substr(v_ltrs1,2,1);
else ltr1a := v_ltrs1;
end if;

if length(v_ltrs2) = 2 then
ltr2a := substr(v_ltrs2,1,1);
ltr2b := substr(v_ltrs2,2,1);
else ltr2a := v_ltrs2;
end if;

start_no := case when length(v_ltrs1) = 1 then (ascii(ltr1a)- 64)
else 26 * (ascii(ltr1a)- 64) + (ascii(ltr1b) - 64) end;
end_no := case when length(v_ltrs2) = 1 then (ascii(ltr2a)- 64)
else 26 * (ascii(ltr2a)- 64) + (ascii(ltr2b) - 64) end;

v_beg_num_val := to_number(v_nums1);
v_end_num_val := to_number(v_nums2);

for l in start_no..end_no
loop
for n in v_beg_num_val..v_end_num_val
loop
if l < 27 then
ret_list := ret_list||chr(l + 64)||to_char(n)||'+';
else ret_list := ret_list||chr(floor(l/26) + 64)||chr(mod(l,26) + 64)
||to_char(n)||'+';
end if;
end loop;
end loop;

return rtrim(ret_list,'+');
end;
/

examples:
SQL> select list_cells('z1:ab2') from dual;

LIST_CELLS('Z1:AB2')
---------------------------------------------
Z1+Z2+AA1+AA2+AB1+AB2

SQL> select list_cells('aa1:ac3') from dual;

LIST_CELLS('AA1:AC3')
---------------------------------------------
AA1+AA2+AA3+AB1+AB2+AB3+AC1+AC2+AC3
0

LVL 35

Expert Comment

ID: 18022596
I'm still curious about why you think it is a good idea to use an Oracle stored procedure to do this.  This is not what Oracle stored procedures do best.
0

Author Comment

ID: 18022601
Once again.... Thank you very much for your timely help.

You are the best....

This site is awesome and I would recommend it to my friends.

Thank you... Thank you... Thank you.

0

Author Comment

ID: 18022683
I sure do agree that the requirement was not specific/defined and also agree that it is not a good idea to use an Oracle stored procedure in the above case but I cannot help it because that is what is the requirement is.

More than anything else I am happy that I got a solution from this site for what I asked for and in a timely manner.

Thank you very much for your help, Guys.
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month5 days, 23 hours left to enroll