[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

PL/SQL LOOP CURSOR

Posted on 2016-11-03
3
Medium Priority
?
118 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 38

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 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

656 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