Home » SQL & PL/SQL » SQL & PL/SQL » Multiplication Funtion In ORACLE (Oracle Database 10g Enterprise Edition Release 10.1.0.2.0)
Multiplication Funtion In ORACLE [message #326540] Wed, 11 June 2008 23:19 Go to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Hi,

I have a table structure like :-

Create table test(A varchar2(50),B NUMBER);

The data in that table is like that:-

A B
----------------------
2*3
2*4*5
4*5
column B contain no data.

I want to create a function which can be used in a select query,and the output should come like that :-

A B
----------------------
2*3 6
2*4*5 40
4*5 20

Means column B contains the resultant value of column A.
And the above output should come through a select statement.You can use any function inside the select statement.

How do I proceed?Please give some idea.

Regards,

Subhadip

Re: Multiplication Funtion In ORACLE [message #326544 is a reply to message #326540] Wed, 11 June 2008 23:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guidelines as stated in URL above

>How do I proceed?Please give some idea.
First post in proper sub-forum & in this case the Homework forum is near the bottom of the frame.

Are you saying that you don't know how to solve 2*3?


Re: Multiplication Funtion In ORACLE [message #326553 is a reply to message #326544] Wed, 11 June 2008 23:43 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
WHAT IS THE WRONG WITH THIS???

I asked for the solution.I need the resultant of column A in column B.In that case how to proceed?
Re: Multiplication Funtion In ORACLE [message #326554 is a reply to message #326553] Wed, 11 June 2008 23:48 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
subhadip.chanda wrote on Wed, 11 June 2008 21:43
WHAT IS THE WRONG WITH THIS???

I asked for the solution.I need the resultant of column A in column B.In that case how to proceed?


>I need the resultant of column A in column B.
So produce the desired results.
It is your problem; not ours!

You are a clueless twit!

Ask until you can no longer speak/type.
NOBODY owes you anything.

You're On Your Own (YOYO)!
Re: Multiplication Funtion In ORACLE [message #326555 is a reply to message #326554] Wed, 11 June 2008 23:50 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
What a meaningless conversation.
Do you have any mis-understanding about my query?
Re: Multiplication Funtion In ORACLE [message #326556 is a reply to message #326555] Wed, 11 June 2008 23:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you want to interpret the contents of a column, you will have to write a procedure to do so.
You might look at dynamic sql, to see if you can construct a sql or a pl/sql block from it, where you catch the outcome.

No need to ask for an example, as you should start doing your own (home)work. We are only here to help, not to do it for you.
Re: Multiplication Funtion In ORACLE [message #326559 is a reply to message #326555] Thu, 12 June 2008 00:00 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
subhadip.chanda wrote on Wed, 11 June 2008 21:50
What a meaningless conversation.
Do you have any mis-understanding about my query?


I understand what you desire.

YOU need to write a basic arithmetic compiler in PL/SQL.
You might want to research Bachus Naur Form (BNF)

We are not here to do your assignment for you.

Re: Multiplication Funtion In ORACLE [message #326560 is a reply to message #326540] Thu, 12 June 2008 00:02 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
I have no idea about solution in pure SQL, as it is not designed to be used for parsing expressions. Maybe some 3rd party package? Or some funky XML approach?

However you may write your own parser (quite difficult if you did not do it before) or use the Oracle one when calling the expression dynamically. Both approaches need the use of PL/SQL.
Re: Multiplication Funtion In ORACLE [message #326561 is a reply to message #326559] Thu, 12 June 2008 00:03 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
I did not assign you anything.I only request some help...
Re: Multiplication Funtion In ORACLE [message #326562 is a reply to message #326561] Thu, 12 June 2008 00:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
subhadip.chanda wrote on Wed, 11 June 2008 22:03
I did not assign you anything.I only request some help...


you need to start with helping YOURSELF.

Stop whining & start coding!
Re: Multiplication Funtion In ORACLE [message #326563 is a reply to message #326540] Thu, 12 June 2008 00:13 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> I only request some help...

So, if you read the responses carefully, you have two ideas how to implement it.
Re: Multiplication Funtion In ORACLE [message #326827 is a reply to message #326540] Thu, 12 June 2008 15:04 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> Create table test(A varchar2(50),B NUMBER)
  2  /

Table created.

SCOTT@orcl_11g> insert all
  2  into test (a) values ('2*3')
  3  into test (a) values ('2*4*5')
  4  into test (a) values ('4*5')
  5  select * from dual
  6  /

3 rows created.

SCOTT@orcl_11g> select * from test
  2  /

A                                                           B
-------------------------------------------------- ----------
2*3
2*4*5
4*5

SCOTT@orcl_11g> create or replace function multiply
  2    (p_a in	 test.a%type)
  3    return	 number
  4  as
  5    v_result  number;
  6  begin
  7    execute immediate 'select ' || p_a || ' from dual' into v_result;
  8    return v_result;
  9  end multiply;
 10  /

Function created.

SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> select a, multiply (a) from test
  2  /

A                                                  MULTIPLY(A)
-------------------------------------------------- -----------
2*3                                                          6
2*4*5                                                       40
4*5                                                         20

SCOTT@orcl_11g> 

Re: Multiplication Funtion In ORACLE [message #326828 is a reply to message #326540] Thu, 12 June 2008 15:07 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Barabra,
That is an INGENIOUS solution!
Re: Multiplication Funtion In ORACLE [message #326878 is a reply to message #326827] Thu, 12 June 2008 23:03 Go to previous messageGo to next message
subhadip.chanda
Messages: 64
Registered: May 2007
Member
Thank you sir.
Re: Multiplication Funtion In ORACLE [message #570070 is a reply to message #326878] Mon, 05 November 2012 07:15 Go to previous messageGo to next message
manubatham20
Messages: 553
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Never came into my mind.

Helped me a lot.
Re: Multiplication Funtion In ORACLE [message #570085 is a reply to message #326540] Mon, 05 November 2012 08:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
subhadip.chanda wrote on Thu, 12 June 2008 00:19
I want to create a function which can be used in a select query,and the output should come like that


If OLAP option is installed:

select  a,
        dbms_aw.eval_number(a) b
  from  test
/

A                                                           B
-------------------------------------------------- ----------
2*3                                                         6
2*4*5                                                      40
4*5                                                        20

SQL> 


If not and you are on 11g:

select  a,
        xmlcast(xmlquery(a returning content) as number) b
  from  test
/

A                                                           B
-------------------------------------------------- ----------
2*3                                                         6
2*4*5                                                      40
4*5                                                        20

SQL> 


If not and you are on older version:

select  a,
        extractvalue(dbms_xmlgen.getxmltype('select ' || a || ' x from dual'),'/ROWSET/ROW/X') b
  from  test
/

A                                                  B
-------------------------------------------------- -----
2*3                                                6
2*4*5                                              40
4*5                                                20

SQL> 


SY.
Re: Multiplication Funtion In ORACLE [message #570086 is a reply to message #570085] Mon, 05 November 2012 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
realize that OP has been absent from forum for 2+ YEARS so why does anyone care about this zombie thread?
Re: Multiplication Funtion In ORACLE [message #570088 is a reply to message #570086] Mon, 05 November 2012 09:25 Go to previous messageGo to next message
manubatham20
Messages: 553
Registered: September 2010
Location: Minneapolis, MN, USA
Senior Member

Yes BlackSwan, its me here reading the suggestion. I don't want to create duplicate thread, so just posted here.

Thanks Solomon.
Re: Multiplication Funtion In ORACLE [message #570089 is a reply to message #570085] Mon, 05 November 2012 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Solomon,

Could you open a new topic in General forum linked to Puzzle menu for all the solutions you see for this kind of issue (formula in columns or related) as there is one for row generator and in which others can add his own.
It does not need to be very well structured.
Thanks.

Regards
Michel
Re: Multiplication Funtion In ORACLE [message #570111 is a reply to message #570089] Mon, 05 November 2012 13:04 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2504
Registered: January 2010
Location: Connecticut, USA
Senior Member
Just did.

SY.
Re: Multiplication Funtion In ORACLE [message #570113 is a reply to message #570111] Mon, 05 November 2012 13:48 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, I will have a look at it tomorrow.

Regards
Michel
Previous Topic: Mutiple conditions in WHERE section
Next Topic: Create Tables Error Statement
Goto Forum:
  


Current Time: Fri Dec 09 23:08:49 CST 2016

Total time taken to generate the page: 0.09612 seconds