Calculation [message #677560] |
Thu, 26 September 2019 05:57 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Dear All,
we have two tables
T1 table having 1 column COL1 varchar2(100), Value of COL1 = :C1+:C2
T2 table having 3 columns A1 Number, C1 Number, C2 Number.
Now the scenario is,
we will enter value in C1 Column= 20 and C2 Column= 30 in Table T2,
Now we need to return 50 in COlumn A1 in T2 Table, because in T1 we have C1+C2, if in Table T1 COL1 having Value :C1*:C2, so value will return 20*30 = 600 in A1 COlumn in table T2.
somebody can help me, how to define the procedure please??
Thankful,
|
|
|
|
|
|
Re: Calculation [message #677564 is a reply to message #677563] |
Thu, 26 September 2019 07:08 |
|
hissam78
Messages: 193 Registered: August 2011 Location: PAKISTAN
|
Senior Member |
|
|
Thankful for reply,
The Scenario is,
We will enter value in C1 Column as 10
and C2 Column as 20
and then user need to select the operator either he want to *, + or - the column values
and then return into A1 column,
so what i think if we make separate table and use C1 and C2 columns
with + sign then it should add the value
if user select with * sign then value will multiply,
we cannot fix the operator as you suggest, because +, - , / user will select dynamically, it will vary row by row.
basically we want to make a formulta on run time.
if you any other idea to fulfill this scenario please share,
hope i clarify my request.
thankful
|
|
|
|
Re: Calculation [message #677566 is a reply to message #677565] |
Thu, 26 September 2019 08:36 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Nobody has said anything about the operators.
It has been suggested that you just calculate the answers whenever you need them rather than storing them in a seperate column in the table.
|
|
|
Re: Calculation [message #677567 is a reply to message #677564] |
Thu, 26 September 2019 09:38 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from tbl
3 /
C1 C2 FORMULA
---------- ---------- -------------------
20 30 c1+c2
20 30 c1-c2
20 30 c1*c2
20 30 c1/c2
20 30 (c1+c2) / (c1 - c2)
SQL> select c1,
2 c2,
3 formula,
4 xmlcast(
5 xmlquery(
6 '/ROWSET/ROW/X'
7 passing dbms_xmlgen.getxmltype(
8 'select ' || formula || ' x from tbl where rowid = ''' || rowid || ''''
9 )
10 returning content
11 )
12 as number
13 ) result
14 from tbl
15 /
C1 C2 FORMULA RESULT
---------- ---------- ------------------- ----------
20 30 c1+c2 50
20 30 c1-c2 -10
20 30 c1*c2 600
20 30 c1/c2 .666666667
20 30 (c1+c2) / (c1 - c2) -5
SQL>
SY.
|
|
|
Re: Calculation [message #677568 is a reply to message #677567] |
Thu, 26 September 2019 09:57 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Actually, straight XMLQUERY will be more efficient:
SQL> select c1,
2 c2,
3 formula,
4 xmlcast(
5 xmlquery(
6 replace(replace(replace(formula,'c1','xs:decimal($c1)'),'c2','xs:decimal($c2)'),'/',' div ')
7 passing c1 as "c1",
8 c2 as "c2"
9 returning content
10 )
11 as number
12 ) result
13 from tbl
14 /
C1 C2 FORMULA RESULT
---------- ---------- ------------------- ----------
20 30 c1+c2 50
20 30 c1-c2 -10
20 30 c1*c2 600
20 30 c1/c2 .666666667
20 30 (c1+c2) / (c1 - c2) -5
SQL>
SY.
|
|
|
Re: Calculation [message #677570 is a reply to message #677568] |
Thu, 26 September 2019 10:10 |
Solomon Yakobson
Messages: 3275 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Ah, I missed you are on 10g:
SQL> select c1,
2 c2,
3 formula,
4 dbms_xmlgen.getxmltype(
5 'select ' || formula || ' x from tbl where rowid = ''' || rowid || ''''
6 ).extract('/ROWSET/ROW/X/text()').getnumberval() result
7 from tbl
8 /
C1 C2 FORMULA RESULT
---------- ---------- ------------------- ----------
20 30 c1+c2 50
20 30 c1-c2 -10
20 30 c1*c2 600
20 30 c1/c2 .666666667
20 30 (c1+c2) / (c1 - c2) -5
SQL>
SY.
|
|
|
|
|
|
|