Need help on inserting price values [message #364263] |
Sun, 07 December 2008 13:53  |
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   |
 |
Littlefoot
Messages: 21823 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   |
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 #364309 is a reply to message #364263] |
Sun, 07 December 2008 22:52  |
flyboy
Messages: 1903 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.
|
|
|