OraFAQ Forum: SQL & PL/SQL » multiple currency value

Home » SQL & PL/SQL » SQL & PL/SQL » multiple currency value (oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

multiple currency value [message #621608]

Thu, 14 August 2014 17:31

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..

Report message to a moderator

Re: multiple currency value [message #621610 is a reply to message #621608]

Thu, 14 August 2014 17:50

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

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.

Report message to a moderator

Re: multiple currency value [message #621612 is a reply to message #621610]

Thu, 14 August 2014 18:27

dariusd7
Messages: 24
Registered: August 2014

Junior Member

But the assignment requires we put it in one.. what about varrays?

Report message to a moderator

Re: multiple currency value [message #621619 is a reply to message #621612]

Fri, 15 August 2014 00:45

Littlefoot
Messages: 21807
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Is it a nested table you are looking for?

Report message to a moderator

Re: multiple currency value [message #621621 is a reply to message #621612]

Fri, 15 August 2014 00:49

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: multiple currency value [message #621622 is a reply to message #621621]

Fri, 15 August 2014 00:50

Littlefoot
Messages: 21807
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).

Report message to a moderator

Re: multiple currency value [message #621623 is a reply to message #621622]

Fri, 15 August 2014 00:53

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: multiple currency value [message #621625 is a reply to message #621623]

Fri, 15 August 2014 00:59

Littlefoot
Messages: 21807
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.

Report message to a moderator

Re: multiple currency value [message #621627 is a reply to message #621625]

Fri, 15 August 2014 01:10

Michel Cadot
Messages: 68641
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.

Report message to a moderator

Re: multiple currency value [message #621655 is a reply to message #621627]

Fri, 15 August 2014 09:30

gazzag
Messages: 1118
Registered: November 2010
Location: Bedwas, UK

Senior Member

Is it possible to forget something yet still remember it? Wink

Report message to a moderator

Re: multiple currency value [message #621656 is a reply to message #621608]

Fri, 15 August 2014 10:20

John Watson
Messages: 8929
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>

Report message to a moderator

Re: multiple currency value [message #621659 is a reply to message #621655]

Fri, 15 August 2014 10:32

Michel Cadot
Messages: 68641
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?

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 21:03:48 CDT 2024