Solved

PL/SQL LOOP CURSOR

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 - query 23 60
SQL Syntax 5 37
Dcount using a date in a table compared to today's date 3 30
sql server computed columns 11 31
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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 video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

773 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