Home » SQL & PL/SQL » SQL & PL/SQL » create table
create table [message #397482] Sat, 11 April 2009 02:04 Go to next message
CLS63AMGV8
Messages: 11
Registered: November 2008
Location: Oman
Junior Member
hi guys
i created table for booking... i want to culcuate number of nights but i got error


create table booking
(bid number primary key,
gid number,
date_from date,
date_to date,
no_of_nights number (sysdate, date_to-date_from));

any help please


Re: create table [message #397483 is a reply to message #397482] Sat, 11 April 2009 02:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
CLS63AMGV8 wrote on Sat, 11 April 2009 08:04
hi guys
i created table for booking... i want to culcuate number of nights but i got error...
any help please


What extra info do you think might be useful here. Whenever you post that you get an error, do you think that it might help to post what error it is that you get?...

no_of_nights number (sysdate, date_to-date_from)
What do you expect to achieve here?
Re: create table [message #397485 is a reply to message #397482] Sat, 11 April 2009 03:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Isn't the same thing that in:
CLS63AMGV8 wrote on Mon, 05 January 2009 06:41
hi guys how are you
i want to (-) but gave me mistake

create view vw_search_rooms as
SELECT BookIngs1.g_Id,
BookIngs1.Date_From,
BookIngs1.Date_To,
Room_Types1.Room_desc,
Room_Types1.Room_Rate,
Rooms1.Room_No,
Rooms1.r_Floor,
Rooms1.r_Suite,
Rooms1.Reserved,
Rooms1.Room_tc,
Rooms1.b_Id,
(sysdate Bookings1.Date_To - Bookings1.Date_From) AS Nights, (Room_Types1.Room_Rate * Nights) AS Total_Amounts
FROM Room_Types1
INNER JOIN (BookIngs1
INNER JOIN Rooms1
ON BookIngs1.b_Id = Rooms1.b_Id)
ON Room_Types1.Room_tc = Rooms1.Room_tc;

And one answer was:
Michel Cadot wrote on Mon, 05 January 2009 07:33
Just read what you wrote in blue.
What is the meaning of (for instance, taking a date for each variable):
05/01/2009 10/12/2008 - 1/11/2008

Regards
Michel

But you didn't reply and I think thought about the question.
And another one was:
BlackSwan wrote on Mon, 05 January 2009 06:46
You have table & provided us no DDL for it.
You have data & provided us no DML for test data.

http://www.orafaq.com/forum/t/88153/0/
You have not read or followed Posting Guideline as stated in URL above.

You have not shown us via CUT & PASTE what you actually did or how Oracle responded.

You have not provided any expected output.

You're On Your Own (YOYO)!

>i want to (-) but gave me mistake

Mistake? what mistake? I don't see any mistake.

But you are still at about the same point asking your question. Read again the last sentences.

Regards
Michel

[Updated on: Sat, 11 April 2009 03:31]

Report message to a moderator

Re: create table [message #397535 is a reply to message #397485] Sun, 12 April 2009 00:33 Go to previous messageGo to next message
CLS63AMGV8
Messages: 11
Registered: November 2008
Location: Oman
Junior Member
and I have oracle develpor 10g

I have tried these:


SQL> create table bookings
2 (bid number,
3 gid_no number,
4 date_from date,
5 date_to date,
6 no_of_nights number (nights=date_to-date_from));
no_of_nights number (nights=date_to-date_from))
*
ERROR at line 6:
ORA-01727: numeric precision specifier is out of range (1 to 38)


SQL> create table bookings
2 (bid number,
3 gid_no number,
4 date_from date,
5 date_to date,
6 no_of_nights NUMBER (date_to - date_from));
no_of_nights NUMBER (date_to - date_from))
*
ERROR at line 6:
ORA-01727: numeric precision specifier is out of range (1 to 38)


SQL> create table bookings
2 (bid number,
3 gid_no number,
4 date_from date,
5 date_to date,
6 no_of_nights NUMBER (SYSDATE, date_to- date_from));
no_of_nights NUMBER (SYSDATE, date_to- date_from))
*
ERROR at line 6:
ORA-01727: numeric precision specifier is out of range (1 to 38)
Re: create table [message #397537 is a reply to message #397482] Sun, 12 April 2009 00:41 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Have you tried RTFM at http://tahiti.oracle.com?

Re: create table [message #397542 is a reply to message #397535] Sun, 12 April 2009 01:46 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
You know what? Instead of trying to convince you that this defintion is totally nonsense, which other people already did and apparently failed:
Remove the no_of_nights column. You don't need it, since you can calculate it from the other columns.
Re: create table [message #397550 is a reply to message #397542] Sun, 12 April 2009 05:13 Go to previous messageGo to next message
CLS63AMGV8
Messages: 11
Registered: November 2008
Location: Oman
Junior Member
thanks....... I tried before by creating view but I thought I can do it automaticly calculating by table when insert date_from and date_to the number of nights automatic calculate. thanks andway
Re: create table [message #397569 is a reply to message #397550] Sun, 12 April 2009 13:39 Go to previous message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, you actually CAN do that - but not within the CREATE TABLE statement. To do that, you'll need to write a database trigger (BEFORE INSERT OR UPDATE might do the job).

However, just as Frank has said, I'd rather NOT store a calculated value (as you can always recalculate it) - your design offers redundant information.
Previous Topic: Regarding NEW_TIME
Next Topic: total marks (merged)
Goto Forum:
  


Current Time: Wed Dec 07 04:40:21 CST 2016

Total time taken to generate the page: 0.10528 seconds