Solved

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

Posted on 2006-11-27
16
809 Views
Last Modified: 2010-10-05
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)
 
Please help me out.

Thank you
0
Comment
Question by:RND2006
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 35

Expert Comment

by:Mark Geerlings
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

by:RND2006
ID: 18020417
Oracle database procedure.

Thank you very much.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
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.

Can you please provide us a little more infomation about your specific business need for this?  There may be other ways to do this.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:RND2006
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

I appreciate your help.
0
 

Author Comment

by:RND2006
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

Please help me out.

Thank you.
0
 

Author Comment

by:RND2006
ID: 18020762
Any thoughts?

Thank you
0
 
LVL 35

Expert Comment

by:Mark Geerlings
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

by:RND2006
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

by:awking00
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

by:Mark Geerlings
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

by:awking00
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

by:RND2006
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

by:
awking00 earned 500 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

by:Mark Geerlings
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

by:RND2006
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

by:RND2006
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question