Home » SQL & PL/SQL » SQL & PL/SQL » multiple currency value (oracle 11g)
multiple currency value [message #621608] Thu, 14 August 2014 17:31 Go to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
I am sitting here with a group of fello students and we are stumped.
This is what it says.
Add a column that contains multiple values for the price in five different currencies. The price is always added in this order:Francs, Yen, Pesos, Pounds, and Euros...

We cannot figure this one out..how do you get a column to accept multiple currency types..and then its says it has to happen in a certain order..seems like it would create some sort of drop down list..

Any suggestions...Oh and we have researched the Oracle.com sight.. I searched under data types.. and really couldn't find anything..
Re: multiple currency value [message #621610 is a reply to message #621608] Thu, 14 August 2014 17:50 Go to previous messageGo to next message
BlackSwan
Messages: 23140
Registered: January 2009
Senior Member
It is bad, poor, problematic & unprofessional to store multiple values in a single column.
the way this erroneous behavior is implemented is by using a comma separated list as a STRING; like as follows '3,5,7,9,1'

The correct way to implement such a requirement is to have two columns, CURRENCY_VALUE & CURRENCY_CODE.
Re: multiple currency value [message #621612 is a reply to message #621610] Thu, 14 August 2014 18:27 Go to previous messageGo to next message
dariusd7
Messages: 24
Registered: August 2014
Junior Member
But the assignment requires we put it in one.. what about varrays?
Re: multiple currency value [message #621619 is a reply to message #621612] Fri, 15 August 2014 00:45 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Is it a nested table you are looking for?
Re: multiple currency value [message #621621 is a reply to message #621612] Fri, 15 August 2014 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I hope you'll NEVER use or promote this way when you'll do real work.
This is really bad design, implementation and will lead to bad performances.

Re: multiple currency value [message #621622 is a reply to message #621621] Fri, 15 August 2014 00:50 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is a school assignment, I suppose (him, sitting with fellow students, all of them being stumped).
Re: multiple currency value [message #621623 is a reply to message #621622] Fri, 15 August 2014 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I'm aware of this but putting a warning the earlier possible in the professional life is not a waste of time and may also profit to other ones who will read this topic.

Re: multiple currency value [message #621625 is a reply to message #621623] Fri, 15 August 2014 00:59 Go to previous messageGo to next message
Littlefoot
Messages: 19884
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, when nested tables were "invented", I thought it was a good idea. However, I found out that they just make things more complicated than they should be, so I never implemented them. This is the right time to remember Black Swan's words, saying that the fact that something can be done, doesn't mean that it should be done.

However, as a student, you don't have a choice - either you implement them, or you fail an exam.
Re: multiple currency value [message #621627 is a reply to message #621625] Fri, 15 August 2014 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

We are a total agreement, this is why I answered to OP's post and not to yours. Smile

@OP, do it now and then forget it and just remember it is a bad idea.

Re: multiple currency value [message #621655 is a reply to message #621627] Fri, 15 August 2014 09:30 Go to previous messageGo to next message
gazzag
Messages: 388
Registered: November 2010
Location: Bristol, UK
Senior Member
Is it possible to forget something yet still remember it? Wink
Re: multiple currency value [message #621656 is a reply to message #621608] Fri, 15 August 2014 10:20 Go to previous messageGo to next message
John Watson
Messages: 4858
Registered: January 2010
Location: Global Village
Senior Member
Here's an example that may help you get started:
orclz>
orclz> create type mytype as varray (3) of number;
  2  /

Type created.

orclz> create table mytab(c1 mytype);

Table created.

orclz> insert into mytab values(mytype(1,2,3));

1 row created.

orclz> select * from mytab;

C1
----------------------------------------------------------
MYTYPE(1, 2, 3)

orclz>

Re: multiple currency value [message #621659 is a reply to message #621655] Fri, 15 August 2014 10:32 Go to previous message
Michel Cadot
Messages: 59981
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

gazzag wrote on Fri, 15 August 2014 16:30
Is it possible to forget something yet still remember it? Wink


Yes, I remember there are nested tables and I remember I must not use them and so I forget how to use (insert, update, delete, select) their entries. That is I forget how to do this (useless) exercise. Razz

Previous Topic: How to eliminate duplicate rows from a table
Next Topic: loading table from excel without using util
Goto Forum:
  


Current Time: Thu Dec 18 10:41:45 CST 2014

Total time taken to generate the page: 0.08872 seconds