Home » SQL & PL/SQL » SQL & PL/SQL » Calculation (Oracle 10G, Windows 2012R2)
Calculation [message #677560] Thu, 26 September 2019 05:57 Go to next message
hissam78
Messages: 109
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 #677561 is a reply to message #677560] Thu, 26 September 2019 06:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
Registered: 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 Go to previous messageGo to next message
hissam78
Messages: 109
Registered: August 2011
Location: PAKISTAN
Senior Member
Thankful for reply,
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.

if you have any idea about this scenario then please share, if in different way.

thanks






Re: Calculation [message #677563 is a reply to message #677562] Thu, 26 September 2019 06:55 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: 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 Go to previous messageGo to next message
hissam78
Messages: 109
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 #677565 is a reply to message #677564] Thu, 26 September 2019 08:22 Go to previous messageGo to next message
BlackSwan
Messages: 26642
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
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 Go to previous messageGo to next message
cookiemonster
Messages: 13755
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2899
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.
Re: Calculation [message #677585 is a reply to message #677567] Fri, 27 September 2019 04:55 Go to previous messageGo to next message
hissam78
Messages: 109
Registered: 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 Go to previous messageGo to next message
hissam78
Messages: 109
Registered: 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
    (Size: 207.82KB, Downloaded 188 times)
Re: Calculation [message #677677 is a reply to message #677676] Fri, 04 October 2019 07:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous message
hissam78
Messages: 109
Registered: 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: Tue Dec 10 00:37:01 CST 2019