Home » SQL & PL/SQL » SQL & PL/SQL » Multiple values in one-many relationship (Oracle 11g R2, pl/sql)
icon5.gif  Multiple values in one-many relationship [message #662843] Sat, 13 May 2017 01:47 Go to next message
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;
Re: Multiple values in one-many relationship [message #662845 is a reply to message #662843] Sat, 13 May 2017 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
These are the tables
Post the CREATE TABLE statements for those.

Quote:
l_cust_id customer.cust_id%type;
Is used nowhere and so is useless.

Quote:
The cust_id is then used to populate the 'cust_id' in the 'order' table.
And how do you know it outside the procedure?

Quote:
Something like this:
The design is wrong.
You should have a UNIQUE constraint on the phone number (as this is your requirement) and just (try to) insert the new customer trapping the exception if the constraint is violated. This will avoid the drawback of your procedure: what if the already existing customer is dropped between the SELECT and the end of the procedure?

Quote:
The 'order' table contains 'order_id' which comes from the 'order_line' table.
Wrong design once again, as I said in one of your previous topics, you first create the order then the order lines.

Quote:
The problem is that the order_line table may or may not contain multiple values of 'order_id'.
How could this come? An order line is part of one and only one order!
I stopped there.

Rethink the whole stuff, your design is wrong.

Re: Multiple values in one-many relationship [message #662848 is a reply to message #662845] Sat, 13 May 2017 02:51 Go to previous message
kaos.tissue
Messages: 94
Registered: May 2017
Member
Quote:
you first create the order then the order lines.
I think this should do it. I'll try doing it. Thanks!
Previous Topic: Error SQL: ORA-02270: no matching unique or primary key for this column-list
Next Topic: Taking time from current day/month/year 06:00:00 to sysdate(Current Time)
Goto Forum:
  


Current Time: Fri Mar 29 07:00:37 CDT 2024