Hello,
I need pl/sql help. I have a table of all orders created by customers. The table has order ID, Customer ID, order type, and order channel. order type can be 'flowers', 'chocolates', 'toys', 'balloons', 'gifts'. order channel can be 'store' or ‘online. I tried to write pl/sql statement that will return a list of customers who created an order for flowers, an order for toys and an order for gifts (so a list of customers who created 3 orders, an order of type flowers, an order of type toys and an order of type gifts. I’m sure the table has data of so many customers who fit this criteria but when I try to run the code no data is returned.. help.. this is what i have:
DECLARE
TYPE customer_list IS TABLE OF VARCHAR2(50);
v_customers customer_list := customer_list();
BEGIN
SELECT customer_id BULK COLLECT INTO v_customers FROM orders WHERE order_type IN ('flowers', 'toys', 'gifts')
GROUP BY customer_id
HAVING COUNT(DISTINCT order_type) = 3;
FOR i IN 1..v_customers.COUNT LOOP DBMS_OUTPUT.PUT_LINE('Customer ID: ' || v_customers(i));
END LOOP;
END;