Multiple values in one-many relationship [message #662843] |
Sat, 13 May 2017 01:47 |
|
kaos.tissue
Messages: 94 Registered: May 2017
|
Member |
|
|
This is a fresh topic with no relation to my previous threads though the tables are same.
These are the tables. These contain other columns too but are irrelevant to the problem so I am not mentioning them.
ORDER(order_id (PK), cust_id (FK) References Customer)
ORDER_LINE((order_id, prod_id (PK)))
CUSTOMER(cust_id (PK), cust_ph)
The cust_id is generated through a sequence. The data flows is as follows:
cust_ph is entered and checked in the 'customer' table. If the cust_ph already exists then the cust_id associated with it is taken else a new cust_id is generated. Something like this:
create or replace procedure INSERT_CUSTOMER
(p_cust_phone customer.cust_phone%type)
is
l_cust_id customer.cust_id%type;
begin
select cust_id
into l_cust_id
from customer c
where c.cust_phone=p_cust_phone;
exception
when no_data_found then
insert into customer
values (cust_id_seq.nextval, p_cust_phone)
returning cust_id into l_cust_id;
end;
The cust_id is then used to populate the 'cust_id' in the 'order' table.
The 'order' table contains 'order_id' which comes from the 'order_line' table. The problem is that the order_line table may or may not contain multiple values of 'order_id'. In such a situation how do I get the unique single value for a particular 'order_id' in 'order' table?
Sample Data in 'order_line':
order_id-prod_id
1---------34
1---------40
1---------88
2---------43
2---------50
Data to be inserted in the 'order' table
order_id------cust_id
1-------------2
2-------------4
Basically group the 'order_id' in the 'order_line' table and insert 1 unique row for each order.
I created the following procedure to do it but it showed me an error when I tried to insert into the 'order' table which was very obvious but I don't know how to resolve it. The error was "single row subquery returned more than one row".
create or replace procedure INSERT_ADDRESS
(p_cust_phone customer.cust_phone%type, p_address address.address%type, p_area address.area%type)
is
l_cust_id customer.cust_id%type;
l_address_id address.address_id%type;
begin
begin
select cust_id
into l_cust_id
from customer c
where c.cust_phone=p_cust_phone;
exception
when no_data_found then
insert into customer
values (cust_id_seq.nextval, 'No Name', p_cust_phone)
returning cust_id into l_cust_id;
end;
insert into address
values (address_id_seq.nextval, UPPER(p_address), UPPER(p_area))
returning address_id into l_address_id;
insert into customer_address
values (l_cust_id, l_address_id);
[b]INSERT INTO customer_order (order_id, cust_id, payment_method_id, order_date)
VALUES ((SELECT order_id FROM order_line),l_cust_id,1,sysdate);[/b]
end;
|
|
|
|
|