SQL*Plus

[sangeetailor]

hi,

i created tables and have out data in, them.

Now i want to go back and set primary keys to some of the tables, how do i do this?

i have tried:

SQL> alter table ORGANISATION modify (
2 primary key (orgcode)
3 );
primary key (orgcode)
*
ERROR at line 2:
ORA-02253: constraint specification not allowed here

and I get the above error. what do i do?

[skhanal]

alter table ORGANISATION add constraint PK_ORGANISATION primary key (orgcode);

[sangeetailor]

thanks for above help.

I have table Private_Event

(Event (PK), CustomerID, Nosexp)

and table Customer

(CustomerID (PK), FirstName, Surname, Address)

For the Private_Event table i want to state that CustomerID is referenced from Customer table.How do I do this? I forgot to do this during the create table stage. I tried the following, which did not work:

SQL> alter table PRIVATE_EVENT modify (
2 customerid number(4) references CUSTOMER
3 );
customerid number(4) references CUSTOMER
*
ERROR at line 2:
ORA-02253: constraint specification not allowed here

[skhanal]

try

alter table private_event add foreign key (customerid)
references customer(customerid);

[sangeetailor]

I have a problem setting foreign keys for one of my tables:

SQL> DESCRIBE EQUIPMENT_BOOKING;
Name Null? Type
----------------------------------------- -------- ----------------------------
RDATE NOT NULL DATE
ROOM_NO NOT NULL VARCHAR2(2)
SESSION_B NOT NULL VARCHAR2(9)
SERIALNO NOT NULL VARCHAR2(18)

RDATE RO SESSION_B SERIALNO
--------- -- --------- ------------------
05-JUL-04 A7 Afternoon 444444444444444444
04-JUL-04 A6 Morning 555555555555555555
05-JUL-04 A7 Afternoon 666666666666666666

===========
AND:
===============

SQL> DESCRIBE ROOM_BOOKING;
Name Null? Type
----------------------------------------- -------- ----------------------------
RDATE NOT NULL DATE
ROOM_NO NOT NULL VARCHAR2(2)
SESSION_B NOT NULL VARCHAR2(9)
EVENT VARCHAR2(30)
LAYOUT VARCHAR2(1)



RDATE RO SESSION_B SERIALNO
--------- -- --------- ------------------
05-JUL-04 A7 Afternoon 666666666666666666
05-JUL-04 A7 Afternoon 444444444444444444
04-JUL-04 A6 Morning 555555555555555555


====

I am trying to set a foreign key from EQUIPMENT_BOOKING to ROOM_BOOKING; so that rdate,room_no,session_b FROM EQUIPMENT_BOOKING is referenced from ROOM_BOOKING.

I tried the below code, and got the following error:


SQL> alter table EQUIPMENT_BOOKING add foreign key(rdate) references ROOM_BOOKING(rdate);
alter table EQUIPMENT_BOOKING add foreign key(rdate) references ROOM_BOOKING(rdate)
*
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

[sangeetailor]

In addition to above problem, I have another. I'm trying to run a query which returns reults of bookings tommorow:

SQL> select mdate,event,quantity
2 from MEAL_BOOKING
3 WHERE mdate = sysdate+1;

I've tried the above, but it does not work.

[skhanal]

It seems you don't have primary key defined in ROOM_BOOKING.

alter table ROOM_BOOKING add constraint PK_ROOM_BOOKING primary key (rdate,room_no,session_b);

alter table EQUIPMENT_BOOKING add foreign key(rdate,room_no,session_b) references ROOM_BOOKING(rdate,room_no,session_b);

[skhanal]

You have to consider time part when you use =. Truncate the time part.


select mdate,event,quantity
from MEAL_BOOKING
WHERE TRUNC(mdate) = TRUNC(sysdate+1);

[sangeetailor]

hi,

regarding the foreign key issue,

i already had primary keys assigned, but it still wouldnt let me reference a foreign ket to the ROOM_BOOKING table.

I even dropped the pk constraint, and re-entered it, but still same problem. There is just a problem referencing to ROOM_BOOKING table.

Thanks for the help on the sysdate query.

[sangeetailor]

in the calculation of the % of occupancy rate, does anyone knw how to stop oracle display the decimal values of the percentage?

eg: 1.11111111111111

removing additional 1's after the decimal point, so only 1 is displayed.


i've done the following to get to the above answer:

select room_no, count(rdate)
FROM ROOM_BOOKING
where rdate in
(select rdate
from ROOM_BOOKING
where rdate between '01APR04' and '30APR04')
group by room_no
having count(rdate) >=0;



SQL> CREATE VIEW room_count
2 as select room_no as rm_no, count(rdate) as countalais
3 FROM ROOM_BOOKING
4 where rdate in
5 (select rdate
6 from ROOM_BOOKING
7 where rdate between '01APR04' and '30APR04')
8 group by room_no
9 having count(rdate) >=0;

View created.

SQL> select rm_no, countalais/90*100 ||'%'|| countalais
2 from room_count;

RM
--
COUNTALAIS/90*100||'%'||COUNTALAIS
--------------------------------------------------------------------------------
A2
1.11111111111111111111111111111111111111%1

A6
1.11111111111111111111111111111111111111%1

B6
2.22222222222222222222222222222222222222%2

[sangeetailor]

Im trying to run a query:

select distinct con_room_count.rm_no,con_room_count.totalbookings
from con_room_count, CONFERENCE_ROOM
where con_room_count.rm_no = CONFERENCE_ROOM.room_no
union
select distinct CONFERENCE_ROOM.room_no,con_room_count.totalbookin gs-con_room_count.totalbookings
from CONFERENCE_ROOM, con_room_count;

the following results are produced:
RM TOTALBOOKINGS
-- -------------
A1 0
A2 0
A2 1
A6 0
A6 2
A7 0
A7 1
B2 0
B3 0
B3 1
B4 0

RM TOTALBOOKINGS
-- -------------
B5 0
B6 0
B6 2
B7 0

15 rows selected

However teh table has duplicated for some room numbers (A7,A6,etc..) we do not want this query to display duplicates.
How can it be done, so that duplicates do not show?

[skhanal]

When creating the foreign key you have to use all columns in the primary key. From the example you provided it looks like you only used rdate.

[skhanal]

To display only one decimal use TRUNC function

select rm_no, trunc(countalais/90*100,2)) ||'%'|| countalais
from room_count;

[skhanal]

For A6 and A7 the room booking numbers are different, they are not duplicate.