Help wanted, sourvenir for the 1st who can solve the problem
Date: Fri, 10 Dec 1999 14:56:39 +0700
Message-ID: <OQ844.3847$HP.31855_at_news>
Hi, I have a problem on calculating yield using SQL*Plus? Whoever can help me solve this problem using SQL*Plus and PL/SQL, I will craft your name using glass-stick and send it to you as a sourvenir no matter where you are. Please give me your name in VARCHAR2(10) and your address in your direct reply to djyoung_at_scholar.co.id
Thanks,
Edy DJ Young
PT Scholar Indonesia
Please follow the instructions :
CREATE TABLE PRODUCTION
(PRODUCT VARCHAR2(20) NOT NULL
,OPERATION NUMBER(4) NOT NULL ,DESCRIPTION VARCHAR2(20) NOT NULL ,QTY_PROCESSED NUMBER(6) NOT NULL ,QTY_DEFECT NUMBER(6) NOT NULL
);
- Product group of VRX-410
INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('VRX-410',1010,'TOUCH-UP',724,5);
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('VRX-410',1020,'ICT',719,2); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('VRX-410',1030,'INSPECTION',717,3); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('VRX-410',1040,'TOUCH-UP',714,5); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('VRX-410',1050,'TOUCH-UP',709,5);
- Product group of MIT-280
INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('MIT-280', 200,'HAND INSERT',1002,0);
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('MIT-280', 300,'SOLDERING',1002,3); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('MIT-280', 400,'CLEAN-UP',999,10); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('MIT-280', 600,'INSPECTION',909,12); INSERT INTO PRODUCTION
(PRODUCT,OPERATION,DESCRIPTION,QTY_PROCESSED,QTY_DEFECT)
VALUES ('MIT-280', 900,'FINAL TEST',1002,0); Then I used the Reporting Script as followed :
- report begin here ---- COLUMN tdate noprint FORMAT A20 NEW_VALUE ttitle_date SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') tdate FROM dual;
TTITLE SKIP 0 -
LEFT "Production Report " -
SKIP 1 -
LEFT "PRODUCT : " ttitle_product -
SKIP 1 -
LEFT "RUN TIME : " ttitle_date
SKIP 1 - LEFT "PAGE : " FORMAT 9999 SQL.PNO SKIP 1 -
LEFT
"--------------------------------------------------------------------------- -----" - SKIP 1
COLUMN product noprint NEW_VALUE ttitle_product
COLUMN operation FORMAT 9999 HEADING "Operation" COLUMN Description FORMAT A20 HEADING "Description" COLUMN qty_processed FORMAT 99,999 HEADING "Processed Qty." COLUMN qty_defect FORMAT 99,999 HEADING "Defect Qty." COLUMN op_yield FORMAT 990.999 HEADING "Op_Yield" COLUMN cum_yield FORMAT 990.999 HEADING "Cum-Yield"
BREAK ON product skip page
COMPUTE sum of qty_processed on product
COMPUTE sum of qty_defect on product
select product,
operation, description, sum(qty_processed) qty_processed, sum(qty_defect) qty_defect, (sum(qty_processed)-sum(qty_defect))/sum(qty_processed)*100 op_yield, 100 cum_yield
from production
group by
product, operation, description;
--- end of report
The result is as followed :
Production Report
PRODUCT : MIT-280
RUN TIME : 10-DEC-1999 13:55:00
Operation Description Processed Qty. Defect Qty. Op_Yield Cum-Yield
--------- -------------------- -------------- ----------- -------- --------- 200 HAND INSERT 1,002 0 100.000 100.000 300 SOLDERING 1,002 3 99.701 100.000 400 CLEAN-UP 999 10 98.999 100.000 600 INSPECTION 909 12 98.680 100.000 900 FINAL TEST 1,002 0 100.000 100.000 -------------- ----------- 4,914 25
Production Report
PRODUCT : VRX-410
RUN TIME : 10-DEC-1999 13:55:00
Operation Description Processed Qty. Defect Qty. Op_Yield Cum-Yield
--------- -------------------- -------------- ----------- -------- --------- 1010 TOUCH-UP 724 5 99.309 100.000 1020 ICT 719 2 99.722 100.000 1030 INSPECTION 717 3 99.582 100.000 1040 TOUCH-UP 714 5 99.300 100.000 1050 TOUCH-UP 709 5 99.295 100.000 -------------- ----------- 3,583 20
The actual report wanted by the production is as followed :
Production Report
PRODUCT : MIT-280
RUN TIME : 10-DEC-1999 13:55:00
Operation Description Processed Qty. Defect Qty. Op_Yield Cum-Yield
--------- -------------------- -------------- ----------- -------- --------- 200 HAND INSERT 1,002 0 100.000 100.000 300 SOLDERING 1,002 3 99.701 99.701 400 CLEAN-UP 999 10 98.999 98.703 600 INSPECTION 909 12 98.680 97.400 900 FINAL TEST 1,002 0 100.000 97.700 -------------- ----------- -------- 4,914 25 97.700
Production Report
PRODUCT : VRX-410
RUN TIME : 10-DEC-1999 13:55:00
Operation Description Processed Qty. Defect Qty. Op_Yield Cum-Yield
--------- -------------------- -------------- ----------- -------- --------- 1010 TOUCH-UP 724 5 99.309 99.309 1020 ICT 719 2 99.722 99.033 1030 INSPECTION 717 3 99.582 98.619 1040 TOUCH-UP 714 5 99.300 97.929 1050 TOUCH-UP 709 5 99.295 97.239 -------------- ----------- -------- 3,583 20 97.239
Here, you can see, with Oracle SQL*Plus, we can't calculate Yield using the COMPUTE function (limited to AVE, SUM, COUNT, etc).
Is it possible to create built-in function to be run with SQL*Plus, so I can
as
simple as saying
COMPUTE yield of op_yield on product
The yield calculation is simple as shown in Cum-Yield If there is a function to perform such calculation :
default cum_yield = 100;
Looping the record from the first record to the end of record : yield = cum_yield*op_yield/100;
The yield should be reset for each product.
Have anyone, DBAs or Oracle Guru know how to perform such calculation using just SQL*Plus and PL/SQL function, without any other third party reporting tools?
Regards,
Edy DJ Young
PT Scholar Indonesia
djyoung_at_scholar.co.id
Received on Fri Dec 10 1999 - 08:56:39 CET