CODEWITHSUNDEEP
loopsjoinplsqliteration
user3484575
user3484575

Reputation: 23

PL/SQL Joining tables inside of an iteration

I'm just trying to self learn PL/SQL procedures over the last week and have run into a few beginner questions around understanding how loops/cursors work with joins in combination.

Any light that someone could shed would be great, as I am quite new to some of the concepts of PL/SQL around cursors.

Apologies in advance for the highly obscure example

In the example below, I am basically starting with table A and trying to obtain the name for the customer and then set a reason_code flag by: a) checking for join success on Table B, if join fails then checking if the customer is in area B (using Table C). In the hypothetical example below, I have also tried to create an output which contains data from another table (Customer Name).

I attempted to make the joins using a similar method to below:

DECLARE

order_cnt integer;

cursor base1 is select customer_id, subscription, reasoncode from customersubscription

BEGIN

for base_rec in base1 loop

select count(*)

into order_cnt

from customersubscription base, customerorder order

where base.customer_id = order.customer_id;

if order_cnt > 1 then

update customersubscription set reasoncode = 'Has ordered product'

where current of base_rec;

else...

END LOOP

END;

As you can see, I have attempted to open a cursor for the base table and iterate through each record, joining to other tables to set a flag. This did not work, I assume its the way i've tried to join as it does not specify the current row. I have also not been able to find any examples of joining for data retrieval in the way I am attempting to derive the customer name

Table A: CustomerSubscription 

Customer_ID | SubscriptionID | reasoncode
123         | 567            |
124         |                |

Table B: CustomerOrder 

Customer_ID | Order_ID | Product
123         | 567      | TITANIC

Table C: Customer 

Customer_ID | Name    | Area     
123         | Roger   | E     
124         | Timothy | B

Output: Table A 

Customer_ID | Name    | ReasonCode     
123         | Roger   | Has ordered Product    
124         | Timothy | Outside of area

Upvotes: 0

Views: 805

Answers (1)

AK47
AK47

Reputation: 3797

Try this,

Select c.Customer_ID 
    ,c.Name
    ,Case When co.Order_id is not null 
            Then 'Has ordered Product'
          When Area ='B'
            Then 'Outside of area'
          Else 'UnKnown'
     End as ReasonCode
 from Customer as c
left join CustomerOrder as co on c.Customer_ID =co.Customer_ID

If you have multiple entries in CustomerOrder table for each Customer then query needs to be changed, as follows,

Select c.Customer_ID 
    ,c.Name
    ,Case When exists(Select COUNT(co.Order_ID) from CustomerOrder as co where c.Customer_ID =co.Customer_ID) 
         Then 'Has ordered Product'
          When Area ='B'
            Then 'Outside of area'
          Else 'UnKnown'
     End as ReasonCode
 from Customer as c

Upvotes: 0

Related Questions