Help wanted, sourvenir for the 1st who can solve the problem

From: Edy DJ Young <djyoung_at_scholar.co.id>
Date: Fri, 10 Dec 1999 14:56:39 +0700
Message-ID: <OQ844.3847$HP.31855@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);

INSERT INTO PRODUCTION
(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);

INSERT INTO PRODUCTION
(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 - 01:56:39 CST