Solved

PL/SQL LOOP CURSOR

Posted on 2016-11-03
3
56 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 37

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Retrieve Values 4 55
Best RAID for a BDD Oracle 4 59
MySQL left join performance 4 30
sort a spool into file output in oracle 1 22
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

932 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

10 Experts available now in Live!

Get 1:1 Help Now