Home » SQL & PL/SQL » SQL & PL/SQL » Insert into
Insert into [message #236264] Wed, 09 May 2007 07:59 Go to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Hi Guys,

Im trying to write a query to insert data with records that have bookings before the 1st of april 1997 into a table called bookingarchive from my booking table.

So far my query looks like this (obviously is not working) Any help would be appreciated.
insert into bookingarchive (select datefrom, dateto from booking where dateto <= '01-apr-1997' and dateto <= '01-apr-1997')


My two tables look like this:

CREATE TABLE Booking (hno char(4), gno char (4), datefrom date, dateto date,
rno char(4), 
CONSTRAINT PK_BOOKING PRIMARY KEY (hno, gno, datefrom),
CONSTRAINT FK_BOOKING_GNO FOREIGN KEY (gno) REFERENCES GUEST(gno),
CONSTRAINT FK_BOOKING_rm FOREIGN KEY (hno, rno) REFERENCES ROOM(hno,rno));


CREATE TABLE BookingArchive (hno char(4), gno char (4), datefrom date, dateto date,
rno char(4),
CONSTRAINT PK_BOOKING_Arch PRIMARY KEY (hno, gno, datefrom),
CONSTRAINT FK_BOOKINGArch_GNO FOREIGN KEY (gno) REFERENCES GUEST(gno),
CONSTRAINT FK_BOOKINGArch_rm FOREIGN KEY (hno, rno) REFERENCES ROOM(hno,rno));
Re: Insert into [message #236271 is a reply to message #236264] Wed, 09 May 2007 08:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tell us why it is not working.

Regards
Michel
Re: Insert into [message #236272 is a reply to message #236264] Wed, 09 May 2007 08:13 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Sorry that would probably help wouldnt it.

Error: ORA-00947

I dont have enough values.


Thats where I was needing peoples help.

Im not sure why my query wont work. I tried as you can see below I was wondering if anyone could help me.
Re: Insert into [message #236274 is a reply to message #236272] Wed, 09 May 2007 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And having 5 columns in the table and 2 in the select statement does not give you an idea why you have "not enough values"?

Regards
Michel
Re: Insert into [message #236275 is a reply to message #236274] Wed, 09 May 2007 08:21 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
yes but im not sure how to correct it and add it into my query thats why I am asking for help.
Re: Insert into [message #236279 is a reply to message #236275] Wed, 09 May 2007 08:31 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
First, if you don't specify which columns to populate, Oracle expects you to populate all columns. Your subselect statement only selects two values and the insert statement expects five. Select all the fields from the booking table to put in the BookingArchive table or specify that you're only going to populate the datefrom and dateto fields in your select statement.

Second, you're going to run into problems with your WHERE clause in your subselect statement. You need to convert the strings you have representing the dates into a DATE type by using the to_date function. I recommend a search on to_date in this forum to find more information on this. Remember, to Oracle, '01-apr-1997' is a string of characters, not a date.

HTH,
Ron
Re: Insert into [message #236281 is a reply to message #236275] Wed, 09 May 2007 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't know how to add the other columns in your select?

OK, that's how:
- move your cursor just after "select "
- type "h"
- type "n"
- type "o"
- type ","
- type " "
- type "g"
- type "n"
- type "o"
- type ","
- type " "
- move cursor just after "dateto"
- type ","
- type " "
- type "r"
- type "n"
- type "o"

Is that ok?

Regards
Michel
Re: Insert into [message #236287 is a reply to message #236279] Wed, 09 May 2007 08:41 Go to previous messageGo to next message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Ronald Beck wrote on Wed, 09 May 2007 23:01
First, if you don't specify which columns to populate, Oracle expects you to populate all columns. Your subselect statement only selects two values and the insert statement expects five. Select all the fields from the booking table to put in the BookingArchive table or specify that you're only going to populate the datefrom and dateto fields in your select statement.

Second, you're going to run into problems with your WHERE clause in your subselect statement. You need to convert the strings you have representing the dates into a DATE type by using the to_date function. I recommend a search on to_date in this forum to find more information on this. Remember, to Oracle, '01-apr-1997' is a string of characters, not a date.

HTH,
Ron



Hi thanks for your reply.

its a string in the table we where given thats why i am using a string.

Also I want to copy everything from one table to the other but only with the condition the row contains those two dates.
Re: Insert into [message #236345 is a reply to message #236287] Wed, 09 May 2007 12:22 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
No, it's not a string in the table. Look at your table definition:

CREATE TABLE Booking (
   hno char(4), 
   gno char (4), 
   datefrom date, 
   dateto date,
   rno char(4), 
CONSTRAINT PK_BOOKING PRIMARY KEY (hno, gno, datefrom),
CONSTRAINT FK_BOOKING_GNO FOREIGN KEY (gno) REFERENCES GUEST(gno),
CONSTRAINT FK_BOOKING_rm FOREIGN KEY (hno, rno) REFERENCES ROOM(hno,rno));


Notice that datefrom and dateto are of DATE type, not CHAR or VARCHAR2. Dates are NOT kept in a DATE type field as a character string. YOU MUST FORMAT DATES when you read them from the table. The DATE display format defaults to DD-MON-YYYY when you don't specify the format using to_char. Run the following...

create or replace procedure show_date_info is
 today_is  date;
begin
  
  select sysdate into today_is from dual;
  
  dbms_output.put_line('DATE FORMAT DD-MON-YYYY: '||to_char(today_is,'DD-MON-YYYY'));
  dbms_output.put_line('DATE FORMAT MM/DD/YYYY: '||to_char(today_is,'MM/DD/YYYY'));
  dbms_output.put_line('DATE FORMAT DD-MON-YYYY HH24:MI:SS: '||to_char(today_is,'DD-MON-YYYY HH24:MI:SS') ); 
  dbms_output.put_line('DATE FORMAT DD-MON-YYYY HH:MI:SSam: '||to_char(today_is,'DD-MON-YYYY HH:MI:SSam') ); 
  dbms_output.put_line('DATE FORMAT fmDay, Month ddth, YYYY: '||to_char(today_is,'fmDay, Month ddth, YYYY')); 
  
end show_date_info;


Notice that the current date and time is put into the variable "today_is" (type DATE) and used with the various formatting options to display the date and time in many different formats.

Also, if you want to copy all the data in each row that satisfies your WHERE clause, you have to include them in the SELECT portion of your query. What you're comparing in the WHERE clause is separate from what you want returned in your SELECT clause. This would also be a valid SQL query

select hno, gno, rno 
from booking 
where dateto <= '01-apr-1997' and dateto <= '01-apr-1997')


although, again you're going to have problems with the dateto piece.

HTH,
Ron
Re: Insert into [message #236348 is a reply to message #236264] Wed, 09 May 2007 12:41 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Power Penguin wrote on Wed, 09 May 2007 08:59

insert into bookingarchive (select datefrom, dateto from booking where dateto <= '01-apr-1997' and dateto <= '01-apr-1997')




Cool name, but this is not how you compare a DATE column to a value. You must use a TO_DATE function on character strings. While your query may work in your environment, it will not work in mine.
FOO SCOTT>alter session set nls_date_format='MM/DD/YYYY'
  2  /

Session altered.

FOO SCOTT>select 1 from dual where sysdate < '01-apr-1997';
select 1 from dual where sysdate < '01-apr-1997'
                                   *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Re: Insert into [message #236397 is a reply to message #236264] Wed, 09 May 2007 18:42 Go to previous message
Power Penguin
Messages: 33
Registered: April 2007
Location: Australia
Member
Thanks heaps Ronald and Joy your help is very much appreciated.
Previous Topic: query rows as columns
Next Topic: Please help
Goto Forum:
  


Current Time: Wed Dec 07 04:41:29 CST 2016

Total time taken to generate the page: 0.08310 seconds