Solved

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

Posted on 2006-11-27
16
783 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
  • 8
  • 4
  • 3
  • +1
16 Comments
 
LVL 34

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 76

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
 

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 34

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 31

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 34

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 31

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 31

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 34

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Required to Import Oracle Database Table 3 43
select query - oracle 16 82
JDeveloper 12c for 32 bit 4 35
EXECUTE IMMEDIATE 5 36
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now