Oracle SQL: Insert based on found value in a column given condition from another table

Question

I want to merge my order data into one table which is now in two separate tables: Order ID and customer code in table Orders:

Order_ID  Customer
1         C11
2         C76
4         C32

and order detalis in table Details (with columns Order_ID, Hour, Quantity) in which the ordered quantity for the hours that the order is valid is given:

Order_ID  Hour Quantity
1         2     10
1         3     20
2         2      5
2         3      5
2         4      5
4         6     20
4         7     25

I want to merge data of these two tables in one table to have only one row per each order by inserting the quantity for the hours that the order is valid in corresponding column, otherwise zero.

Order_ID  Cutomer  Hour1 Hour2 Hour3 Hour4 Hour5 Hour6 Hour7 ...
1         C11       0     10    20    0     0     0     0
2         C76       0      5     5    5     0     0     0
4         C32       0      0     0    0     0    20    25

I tried (only for quantity of hour 1):

insert into Merged_Order_Table 
(Order_ID,Customer,Hour1)
select 
Orders.Order_id,Orders.Customer,
case 
when 1 in (select Details.Hour from Details,Orders where 
Details.Order_ID = Orders.Order_ID)
then Details.Quantity
else 0
end
from 
Orders
inner join
Details
on
Details.Order_ID = Orders.Order_ID;

But got quantity in Hour1 even for orders with no quantity in this hour.

What will happen when you'll get an hour not present in the Details table, you'll have the change the table structure? That said search for PIVOT — Serpiton
– Serpiton, Commented May 21, 2014 at 15:38
As a beginner I think I need more help. My query is obviously wrong, because I get this for Order_ID=1 when I run it (2 rows): Order_ID Cutomer Hour1 1 C11 10 1 C11 10 while this order has no quantity in Hour1. My expected output for this query is: Order_ID Cutomer Hour1 1 C11 0 — Matin
– Matin, Commented May 21, 2014 at 15:43
It's not about the query, it's about the idea. What will happen if tomorrow a new Hour value, make it 47, appear in Details? The new table will not have it and you'll need to drop and recreate it. Unless you are sure that the values for the Hour fields are completely mapped this can happen (and it can still happen even if you think you have the data mapped). A SELECT that return the pivoted data make more sense. — Serpiton
– Serpiton, Commented May 21, 2014 at 15:53
Aha, I am only using this merged table as an auxiliary table to extract the "structure" of orders. I am not removing the original tables, but only don't know how to write a query that outputs structure (only Hour_xs). Later I need to run another query on the orders with "identical" structure. So, it is my humble way to do it :) — Matin
– Matin, Commented May 21, 2014 at 15:59

Gordon Linoff · Accepted Answer · 2014-05-21 15:41:34Z

I question why you would want to take nicely normalized data and put it into a table with that structure. I can understand a query returning the data like that, but another table?

In any case, your problem is a common problem when using correlated subqueries. The table being correlated is included in the subquery. Ooops. Here is fix for that:

insert into Merged_Order_Table(Order_ID, Customer, Hour1)
    select o.Order_id, o.Customer,
           (case when 1 in (select d.Hour from Details d where d.Order_ID = o.Order_ID)
                 then d.Quantity
                 else 0
            end)
    from Orders o;

That said, what you really want is conditional aggregation:

insert into Merged_Order_Table(Order_ID, Customer, Hour1)
    select o.Order_id, o.Customer,
           sum(case when d.Hour = 1 then d.Quantity else 0 end)
    from Orders o left join
         Details d
         on o.Order_ID = d.Order_ID
    group by o.Order_id, o.Customer;

vav · Accepted Answer · 2014-05-21 15:41:13Z

1

You are on a right track!

you just need one more layer:

select order_id, customer, max(quantity) hour1 from 
(your query)
group by order_id, customer

Or you can look into how to do PIVOT tables

answered May 21, 2014 at 15:41

vav

4,6942 gold badges22 silver badges40 bronze badges

Collectives™ on Stack Overflow

Oracle SQL: Insert based on found value in a column given condition from another table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related