Solved

PL/SQL LOOP CURSOR

Posted on 2016-11-03
3
40 Views
Last Modified: 2016-11-04
I'm new to Oracle PL/SQL and need assistance with the following.  I have a table that is a sales order line table.  The first 2 line for each sales order is use for quote (labor and material).  I want to read that table and take the other lines in that's not quote and subtract from the first 2 lines depending on if it's material or labor.

The image below is the select statement to pull from the CUST_ORDER_DETAIL table.  In the description, it is listed as LABOR for labor quote and MATERIAL for material quote.  This sales order, we'll keep adding additional labor and material lines as we progress.  Example, sales order 12121 has 5 lines, but we can be adding more lines going forward.  If it's labor, we populate the "SERVICE_ID" field.  If it's material we will populate "DESCRIPTION", but will not put in keyword "LABOR" or "MATERIAL" since that is reserve for quote.
cust_order_detail table
My output result is below.  As we continue to add in additional lines, it should look at the "USER_AMT" field and take that away from the total lines that's not quote for that same sales order.
Result
Here's my attempt at it, but don't think it is right.  Can someone take a look and see what I did wrong?  I do not know if I need 2 cursors. Basically request is to make sure that the USER_AMT is populated so I can use that as a reference point of the quoted amount since "UNIT_PRICE" will be changing as we add more lines.  Then second request is to take total material or labor (excluding quote from line 1 and 2) and subtract from "USER_AMT" and then update "UNIT_PRICE" with the difference for the "MATERIAL" or "LABOR" quote line.

DECLARE 

  V_LABOR_AMT NUMBER(8,4);
  V_MATERIAL_AMT NUMBER(8,4);

CURSOR C1 IS
SELECT  COD.ORDER_ID, COD.LINE, COD.UNIT_PRICE, COD.SERVICE_ID, COD.DESCRIPTION, COD.USER_AMT
FROM    CUST_ORDER_DETAIL COD
WHERE   COD.ORDER_ID IN ('12121', '11233')
ORDER BY COD.ORDER_ID, COD.LINE;

CURSOR C2 IS
SELECT  COD.ORDER_ID, COD.LINE, COD.UNIT_PRICE, COD.SERVICE_ID, COD.DESCRIPTION, COD.USER_AMT
FROM    CUST_ORDER_LINE COD
WHERE   COD.ORDER_ID IN ('12121', '11233')
ORDER BY COD.ORDER_ID, COD.LINE;

BEGIN

V_LABOR_AMT := 0; 
V_MATERIAL_AMT := 0;


    FOR R IN C1
    LOOP
      --LABOR
      IF R.DESCRIPTION = 'LABOR' AND R.USER_AMT IS NULL  THEN
       
        --IF USER_AMT IS NULL THEN UPDATE UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    USER_AMT = R.UNIT_PRICE
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'LABOR';
          COMMIT;
      --MATERIAL
      ELSIF R.DESCRIPTION = 'MATERIAL' AND R.USER_AMT IS NULL THEN

         --IF USER_AMT IS NULL THEN UPDATE WITH UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    USER_AMT = R.UNIT_PRICE
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'MATERIAL';
          COMMIT;
      END IF;
    END LOOP;
      
    FOR I IN C2
    LOOP
      --LABOR
      IF I.DESCRIPTION NOT IN ('LABOR', 'MATERIAL') AND R.SERVICE_ID IS NOT NULL  THEN
        V_LABOR_AMT := V_LABOR_AMT + I.UNIT_PRICE
        
        --IF USER_AMT IS NULL THEN UPDATE UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    UNIT_PRICE = R.USER_AMT - SUM(V_LABOR_AMT)
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'LABOR';
          COMMIT;
      --MATERIAL
      ELSIF R.DESCRIPTION NOT IN ('LABOR', 'MATERIAL') AND R.SERVICE_ID IS NULL THEN
        V_MATERIAL-AMT := V_MATERIAL_AMT + I.UNIT_PRICE
        
         --IF USER_AMT IS NULL THEN UPDATE WITH UNIT_PRICE
          UPDATE CUST_ORDER_LINE
          SET    UNIT_PRICE = R.UNIT_AMT - SUM(V_MATERIAL_AMT)
          WHERE  ORDER_ID = R.ORDER_ID AND DESCRIPTION = 'MATERIAL';
          COMMIT;
      END IF;
    END LOOP;
    
END;

Open in new window

0
Comment
Question by:holemania
3 Comments
 
LVL 36

Expert Comment

by:Geert Gruwez
ID: 41872412
why commit inside the procedure ?
and why so many times ?

do you now the "case" clause  ?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 41872530
Please post your sample data as text so we can copy and paste it.

No, you don't need two cursors.  Just like your previous question, I'm also not convinced you need the loop.

The cursor is selecting from CUST_ORDER_DETAIL and you are updating CUST_ORDER_LINE.

The first image shows CUST_ORDER_DETAIL.  I'm assuming the second image is an updated CUST_ORDER_DETAIL.

I don't see that in your coded attempt.

Anyway, I can take the data from the first image and generate the data in the second image from a single SELECT statement.

I don't see where the CUST_ORDER_LINE table is part of this?

Here is my test setup and single SELECT that produces the second image from the first:
create table CUST_ORDER_DETAIL (
		ORDER_ID VARCHAR2(6),
		LINE number,
		UNIT_PRICE number,
		service_id VARCHAR2(10),
		description VARCHAR2(15),
		user_amt number
);

insert into cust_order_detail values('12121',1,3000,null,'LABOR',null);
insert into cust_order_detail values('12121',2,2000,null,'MATERIAL',null);
insert into cust_order_detail values('12121',3,120,'INSTALL',null,null);
insert into cust_order_detail values('12121',4,100,null,'ALUM. SHEET',null);
insert into cust_order_detail values('12121',5,5,null,'2X3 BOLT',null);

insert into cust_order_detail values('11233',1,1000,null,'LABOR',null);
insert into cust_order_detail values('11233',2,500,null,'MATERIAL',null);
insert into cust_order_detail values('11233',3,120,'INSTALL',null,null);
insert into cust_order_detail values('11233',4,5,null,'2X3 BOLT',null);
commit;

select order_id,
	line,
	case description
		when 'LABOR' then user_amt-total_labor
		when 'MATERIAL' then user_amt-total_material
		else unit_price end unit_price,
	service_id,
	description,
	user_amt
from (
	select
	order_id, line, unit_price, service_id, description,
		case when line in (1,2) then unit_price else user_amt end  user_amt,
		sum(case when line>3 then unit_price end) over(partition by order_id) total_material,
		sum(case when service_id='INSTALL' then unit_price end) over(partition by order_id) total_labor
	from cust_order_detail
)
order by order_id, line
/

Open in new window

0
 

Author Closing Comment

by:holemania
ID: 41874244
Thank you this helps a lot.  With the cursor/loop, I am doing other things within it, but if i can incorporate more into straight query, that would be even better.  Thanks again.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now