Home » SQL & PL/SQL » SQL & PL/SQL » Calculation (Oracle 10G, Windows 2012R2)
Calculation Thu, 26 September 2019 05:57
 hissam78 Messages: 113Registered: 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 #677561 is a reply to message #677560] Thu, 26 September 2019 06:32
 Solomon Yakobson Messages: 2976Registered: January 2010 Location: Connecticut, USA Senior Member
How do you know what table T1 row corresponds to what table T2 row?

SY.
Re: Calculation [message #677562 is a reply to message #677561] Thu, 26 September 2019 06:40
 hissam78 Messages: 113Registered: August 2011 Location: PAKISTAN Senior Member
This is what we need, if we enter C1 Column value in T2 table and C2 Column value in T2 table, because we define C1+C2 in Table T1,
so Value of C1 and C2 in T2 TABLE should return the Value in A1 in T2 table after making sum because there is + sign in C1 and C2.

basically we want to make formula,
if there is + between C1 and C2 so sum should perform in T2 and enter in A1 column in Table T2.
if there is - between C1 and C2 so minus should perform in T2 and enter in A1 column in Table T2.
if there is / between C1 and C2 so division should perform in T2 and enter in A1 column in Table T2.
and so on.

thanks

Re: Calculation [message #677563 is a reply to message #677562] Thu, 26 September 2019 06:55
 EdStevens Messages: 1229Registered: September 2013 Senior Member
Why are you even trying to store a value that can (it appears) always be calculated at run time? This violates a fundamental principle of relational data design, and is very prone to getting the stored/calculated value to be out of sync with reality.

Please read the FAQ on how to post a problem. Especially note items 7 through 10.
Re: Calculation [message #677564 is a reply to message #677563] Thu, 26 September 2019 07:08
 hissam78 Messages: 113Registered: August 2011 Location: PAKISTAN Senior Member
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 #677565 is a reply to message #677564] Thu, 26 September 2019 08:22
 BlackSwan Messages: 26730Registered: January 2009 Location: SoCal Senior Member
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Calculation [message #677566 is a reply to message #677565] Thu, 26 September 2019 08:36
 cookiemonster Messages: 13894Registered: 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: 2976Registered: 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: 2976Registered: 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: 2976Registered: 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.
Re: Calculation [message #677585 is a reply to message #677567] Fri, 27 September 2019 04:55
 hissam78 Messages: 113Registered: August 2011 Location: PAKISTAN Senior Member
i am really grateful to all my genius Experts, you all are incredible and very helpful for solving our problems. great result this is what we need. thankful
Re: Calculation [message #677676 is a reply to message #677570] Fri, 04 October 2019 05:59
 hissam78 Messages: 113Registered: August 2011 Location: PAKISTAN Senior Member
Dear Respectful Experts,

After running the query following error showing attached in jpg format.we are working in oracle 10g, please help
• Attachment: DBMS.jpg
Re: Calculation [message #677677 is a reply to message #677676] Fri, 04 October 2019 07:23
 Michel Cadot Messages: 67288Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator

You have to change "tbl" by your own table name ("A_TABLE") in the dbms_xmlgen expression.

Re: Calculation [message #677684 is a reply to message #677677] Sat, 05 October 2019 06:52
 hissam78 Messages: 113Registered: August 2011 Location: PAKISTAN Senior Member
wooow Thanks a lot, Dear Michel Cadot the great, Many Thanks, Got it!!!
 Previous Topic: ORA-29279: SMTP permanent error: 550 domain. Next Topic: display Old value and new value as per date in single row
Goto Forum:

Current Time: Thu Aug 06 20:41:29 CDT 2020