Home » SQL & PL/SQL » SQL & PL/SQL » Need help on inserting price values
Need help on inserting price values [message #364263] Sun, 07 December 2008 13:53 Go to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
Hi, we created a couple tables such as :
CREATE TABLE VENTE
(NO_VENTE INTEGER,
CODE_CROISIERE INTEGER NOT NULL,
NO_CABINE INTEGER NOT NULL,
PRIX NUMERIC(7,2) NOT NULL,
NO_PERSONNE_CLIENT INTEGER NOT NULL,
CONSTRAINT PK_VENTE PRIMARY KEY (NO_VENTE),
CONSTRAINT FK_VENTE_CROISIERE FOREIGN KEY (CODE_CROISIERE) REFERENCES CROISIERE (CODE_CROISIERE),
CONSTRAINT FK_VENTE_CLIENT FOREIGN KEY (NO_PERSONNE_CLIENT) REFERENCES CLIENT (NO_PERSONNE));

For the value "PRIX" (which means price), we need to insert values in a ***.**$ format, but it does not work the way we did it :

INSERT INTO VENTE VALUES (NOVTS_SEQ.NEXTVAL, 100,'A222', *******, 1000);

where the **** are, it is where the price should be
Thank you very much for your time.
Re: Need help on inserting price values [message #364265 is a reply to message #364263] Sun, 07 December 2008 13:57 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Ummm, no, you don't really want to do that.

If you'd like to store a formatted number (your request), you'd have to put it into a CHARACTER datatype column, which would be a bad idea.

Therefore, store prices as numbers, don't care about formatting during INSERT; however, format it as you wish during SELECT (in your reports, for example) with a little help of the TO_CHAR function.
Re: Need help on inserting price values [message #364266 is a reply to message #364263] Sun, 07 December 2008 14:00 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
TYhank your for the quick reply.
Unfortunately, our teacher wants us to use the format (7,2) <-- which means 7 digits, with 2 decimals...
What we can't figure out is how to insert those values into the table. Thank you again!
Re: Need help on inserting price values [message #364269 is a reply to message #364263] Sun, 07 December 2008 14:27 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
Wow, I think the problem is elsewhere in the code... we tried switching to an integer just to test it and it still says "Invalid number"
Anyone got a clue??
Re: Need help on inserting price values [message #364271 is a reply to message #364269] Sun, 07 December 2008 14:31 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Just what are you trying to insert:

SQL> desc price
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRIX                                               NUMBER(7,2)

SQL> insert into price values (1, 12.34);

1 row created.

SQL> insert into price values (2, 12345.67);

1 row created.

SQL> insert into price values (3, 123456.78);
insert into price values (3, 123456.78)
                             *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select * from price;

        ID       PRIX
---------- ----------
         1      12.34
         2   12345.67
Re: Need help on inserting price values [message #364272 is a reply to message #364263] Sun, 07 December 2008 14:38 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
It works!
Thank you VERY much!
Re: Need help on inserting price values [message #364273 is a reply to message #364272] Sun, 07 December 2008 14:40 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What works?

I'd really love to see how you managed to insert ***.**$ value into the NUMBER(7, 2) column.
Re: Need help on inserting price values [message #364274 is a reply to message #364263] Sun, 07 December 2008 14:41 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
Well exactly how he said, but we don't have the $ symbol. No biggie! I'll put it in the table name.
Re: Need help on inserting price values [message #364275 is a reply to message #364274] Sun, 07 December 2008 14:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I must be missing something here ...

Are you overwhelmed by discovering that
SQL> create table test (col number(7, 2));

Table created.

SQL> insert into test (col) values (123.45);

1 row created.

SQL>
works, and it answers your question? If so, oh well, fine (as far as I'm concerned).

However, what does
Quote:
I'll put it (the '$' sign) in the table name.
mean? How do you plan to put it into the table name, why would you want to do it and how would it help solving the problem completely?

As far as I can tell, my first reply is still valid.
Re: Need help on inserting price values [message #364276 is a reply to message #364263] Sun, 07 December 2008 14:50 Go to previous messageGo to next message
DrBallard
Messages: 20
Registered: October 2008
Junior Member
I just mean that when I create the tables attributes I'll rename the column to Price ($).
Just like that Very Happy
Re: Need help on inserting price values [message #364278 is a reply to message #364274] Sun, 07 December 2008 14:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select to_char(12345.57,'$99990.00') from dual;
TO_CHAR(12
----------
 $12345.57

1 row selected.

Regards
Michel
Re: Need help on inserting price values [message #364279 is a reply to message #364276] Sun, 07 December 2008 14:59 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DrBallard wrote on Sun, 07 December 2008 21:50
I just mean that when I create the tables attributes I'll rename the column to Price ($).

But, your teacher said that you
Quote:
need to insert values in a ***.**$ format

By the way, did you try to rename a column so that its name is "Price ($)"?
Re: Need help on inserting price values [message #364280 is a reply to message #364278] Sun, 07 December 2008 14:59 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
DrBallard wrote on Sun, 07 December 2008 14:53

we need to insert values in a ***.**$ format

or maybe as erroneously listed in the OP's initial post ??

SQL> select prix||'$' from price;

PRIX||'$'
-----------------------------------------
12.34$
12345.67$

It's all a guess with this one Smile
Re: Need help on inserting price values [message #364309 is a reply to message #364263] Sun, 07 December 2008 22:52 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
DrBallard wrote on Sun, 07 December 2008 14:53
we need to insert values in a ***.**$ format

I understand it as homework assignment on converting string with given format to a number (TO_NUMBER function with proper number format model using decimal character and currency symbols).
Of course, the exact assignment (or its translation) would make clear, what is really required.
Previous Topic: Export rows with all dependent rows
Next Topic: unique records
Goto Forum:
  


Current Time: Thu Dec 08 16:20:06 CST 2016

Total time taken to generate the page: 0.10975 seconds