Home » SQL & PL/SQL » SQL & PL/SQL » how to handle this problem (Oracle9i Enterprise Edition Release
how to handle this problem [message #321084] Mon, 19 May 2008 00:42 Go to next message
Messages: 65
Registered: November 2006
Location: Mumbai

Hi All,

If i give the input like (d10/h72)*30
then the ouput sholud be (d10_30)/h72. And also i have to store these values in a table

Formula              Operator
========             ----------
d10_30                    *        

h72                        /

for d10_30 the oprator is * because we are multiplying d10 with 30 and also for the second record its division. Actually the input is a column in table.

Iam giving one more example

input: (((A+B)/x)+C)*D

1st step: ((A+B+Cx)/x)*D

2nd step: (AD+BD+CDx)/x

3rd step :(A_D+B_D+C_D_x)/x

And the value in the 3rd step i have to store in the database like below.

Formula              Operator
========             ----------
A_D                    *        

b_D                    * 

C_D_X                  * 

x                       /

for this i wrote a function

create or replace function pp_sf return  number is

cursor cur_1 is select long_formula from p_key_chk;
str_len number;
str varchar2(500):= null;
str1 varchar2(500);
asci_num number;
max_num number;
opr varchar2(5);
str_opr varchar2(2);
str_opr1 varchar2(2);
max_cnt number;
f_opr number;
f_opr_s varchar2(10);

--cursor cur_2 is select variables from p_key_chk_k where num = (select max(num) from p_key_chk_k)

for i in cur_1
 select length(i.long_formula) into str_len from dual;
  for j in 1..str_len
    select ascii(substr(i.long_formula,j,1)) into asci_num from dual;
     if(asci_num > 39 and asci_num <48 ) then
       if (str is not null) then
         insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
         SEQ := SEQ+1;
         str := null;
       end if;
      if(asci_num = 40 ) then
        select count(num) into max_cnt  from  p_key_chk_k;
        if max_cnt = 0 then
           max_num := 1;
           select max(num) into max_num from p_key_chk_k;
           max_num := max_num+1;
        end if;
     end if;
          select chr(asci_num) into str from dual;
          insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
          SEQ := SEQ+1;
          if str=')' then
          max_num := max_num - 1;
          if(max_num = 0 ) then 
           max_num := null;
          end if;
          end if;
          str := null ;
       select chr(asci_num) into str1 from dual;
       str := str||str1;
       if j = str_len then
        insert into p_key_chk_k(sr_no,variables,num) values (SEQ,str,max_num);
        SEQ := SEQ+1;
        str := null;
       end if;
        end if;
    end loop;
  end loop;
  return 0;
end pp_sf;

its giving out put like :

SQL> select * from p_key_chk_k ;

---------- --------- ------ ------
K89                              1
+                                2
k9                               3
+                                4
AA9                              5
+                                6
C89                              7
/                                8
(                         1      9
D77                       1     10
+                         1     11
E15                       1     12
)                         1     13
*                               14
E15                             15

15 rows selected

now i have to get the second step
for ex:

((A+B)/x)+C------> (A+B+Cx)/x

and also i have to store the operator value in the table.

please adivise me for this problem.

the table structures are given below

desc p_key_chk_k
Name      Type         Nullable Default Comments 
--------- ------------ -------- ------- -------- 
VARIABLES VARCHAR2(10) Y                         
OPERATORS VARCHAR2(2)  Y                         
NUM       NUMBER(5)    Y                         
SR_NO     NUMBER(5)    Y                         

SQL> desc p_key_chk
Name         Type          Nullable Default Comments 
------------ ------------- -------- ------- -------- 
LONG_FORMULA VARCHAR2(500) Y          

Re: how to handle this problem [message #321331 is a reply to message #321084] Mon, 19 May 2008 22:10 Go to previous message
Messages: 1840
Registered: November 2006
Senior Member
This is rather algorithm problem than Oracle one. However when solving it, you shall first learn at least the basics of parsing process, as the given code does not seem to have any idea of them.
According to this I would choose a tree-like format rather than this sequential one; but this may be also satisfying (just for storing purposes). I did not cope with parsers for years, so I may be mistaken in this.

I see just one way of processing - load it into the tree structure, apply some transformations and store it back. You shall specify the rules more exactly than you did - how to change the order of operators and, more important, whether to do so (eg. multiplication shall be computed in the highest preference).
Previous Topic: quering list partition
Next Topic: chr() + tab space + xls file format
Goto Forum:

Current Time: Tue May 23 05:44:54 CDT 2017

Total time taken to generate the page: 0.11162 seconds