Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sql query help-experts and gurus please help

makdutakduFeb 3 2010 — edited Feb 4 2010
hi experts and gurus
i am posting the create table statements of tables i am using
 
CREATE TABLE XXNP_OPN_JOBLOG_001 E
(
  OPN_JOBLOG_001_ID       NUMBER,
  OPN_JOB_ID              VARCHAR2(240 BYTE),
  OPN_JOB_DESC            VARCHAR2(240 BYTE),
  SLURRY_YIELD            NUMBER,
  SLURRY_VOL_ACTUAL       NUMBER,
  ORG_ID                  NUMBER(15),
  MANUAL                  VARCHAR2(1 BYTE)
  
  
)
 
CREATE TABLE XXNP_OPN_JOBLOG_EST_002 F
(
  OPN_JOBLOG_002_ID  NUMBER,
  OPN_JOBLOG_007_ID  NUMBER,
  INVENTORY_ITEM_ID  NUMBER,
  ITEM_PERCENT       NUMBER,
  OPN_AMOUNT         NUMBER,
  OPN_JOBLOG_001_ID  NUMBER,
  OPN_JOBLOG_006_ID  NUMBER
  
)
 
 
CREATE TABLE XXNP_OPN_JOBLOG_RES_005 J
(
  OPN_JOBLOG_005_ID  NUMBER,
  OPN_JOBLOG_001_ID  NUMBER,
  OPN_VALUE          NUMBER,
  OPN_RESOURCE_DESC  VARCHAR2(500 BYTE)
  
  
)
 
 
 
CREATE TABLE MTL_SYSTEM_ITEMS_B P
(
  INVENTORY_ITEM_ID               NUMBER        NOT NULL,
  ORGANIZATION_ID                 NUMBER        NOT NULL,
   ATTRIBUTE5                      VARCHAR2(240 BYTE),
  
  
  
)
 
 
 
SQL> select OPN_JOBLOG_001_ID ,OPN_JOB_ID , OPN_JOB_DESC ,SLURRY_TYPE , SLURRY_YIELD ,SLURRY_VOL_ACT
UAL ,ORG_ID  , MANUAL  from xxnp_opn_joblog_001 E where opn_job_desc= 'KOC/KDC-26/RA-277/6' 
          
  OPN_JOBLOG_001_ID  OPN_JOB_ID OPN_JOb_DESC
    291                                 2460                KOC/KDC-26/RA-277/6
 
SLURRY_TYPE  SLURRY_YIELD  SLURRY_VOL_ACTUAL   ORG_ID  MANUAL
                                                                                           0             N
 
 
 
 
 
 
 
SQL>  select OPN_JOBLOG_002_ID  ,
  2   OPN_JOBLOG_007_ID  ,
  3   INVENTORY_ITEM_ID  ,
  4    ITEM_PERCENT       ,
  5   OPN_JOBLOG_001_ID , OPN_JOBLOG_006_ID 
  6   from XXNP_OPN_JOBLOG_EST_002 F where  OPN_JOBLOG_001_ID='291'
  7  / 
 
OPN_JOBLOG_002_ID OPN_JOBLOG_007_ID INVENTORY_ITEM_ID ITEM_PERCENT OPN_JOBLOG_001_ID
----------------- ----------------- ----------------- ------------ -----------------
OPN_JOBLOG_006_ID
-----------------
             1342               321             19815                            291
              229
 
             1343               321             19821          .45               291
              229
 
             1344               321             19838          .25               291
              229
 
             1345               321             19606           16               291
              229
 
             1346               321             24318          .01               291
              229
 
             1347               322             19816                            291
              229
 
             1348               322             19822           .6               291
 
OPN_JOBLOG_002_ID OPN_JOBLOG_007_ID INVENTORY_ITEM_ID ITEM_PERCENT OPN_JOBLOG_001_ID
----------------- ----------------- ----------------- ------------ -----------------
OPN_JOBLOG_006_ID
-----------------
              229
 
             1349               322             19838           .1               291
              229
 
             1350               322             19570           .8               291
              229
 
             1351               322             24318         .005               291
              229
 
             1352               322             19634            3               291
              229
 
             1353               322             19638           50               291
              229
 
             1354               322             19639          100               291
              229
 
OPN_JOBLOG_002_ID OPN_JOBLOG_007_ID INVENTORY_ITEM_ID ITEM_PERCENT OPN_JOBLOG_001_ID
----------------- ----------------- ----------------- ------------ -----------------
OPN_JOBLOG_006_ID
-----------------
 
 
13 rows selected.
I require to include column attribute5 from MTL_SYSTEM_ITEMS_B P table inorder to perform the calculation for sbload as follows
sum(cubicfeet)+sum(poundattribute5)=sbload* 
i am now giving the first query  given below for which the o/p is perfect
 
select (SUM(case when F.ITEM_UOM = 'Cubic Feet' then
            ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/H.SLURRY_YIELD))ELSE 0 end)*(0.047) +
      sum(case when F.ITEM_UOM =  'Pound' then
           Ceil((ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/DECODE(H.SLURRY_YIELD,0,NULL,H.SLURRY_YIELD)))*94)*F.ITEM_PERCENT/100 )ELSE 0 end)*0.0005)*(j.opn_value/2)transport,
        SUM(case when F.ITEM_UOM = 'Cubic Feet' then
            ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/H.SLURRY_YIELD))end)sload ,
      e.opn_job_desc,J.OPN_VALUE---e.invoice_ref,e.invoice_ref_m,f.item_number,f.item_name,f.item_uom,f.description,J.OPN_VALUE
     FROM xxnp_opn_joblog_001 E,
 
         XXNP_OPN_JOBLOG_STAGE_002 G,
         XXNP_OPN_JOBLOG_SLURRY_003 H,
 
           XXNP_OPN_JOBLOG_RES_005 J,
         XXNP_OPN_JOBLOG_EST_002   F
WHERE E.OPN_JOB_DESC   = 'KOC/KDC-26/RA-277/6' AND E.MANUAL='N'
         and J.opn_resource_desc='4X4  PICK-UP OR LIGHT VEHICLES'
          AND E.OPN_JOBLOG_001_ID = J.OPN_JOBLOG_001_ID
     AND E.OPN_JOBLOG_001_ID = G.OPN_JOBLOG_001_ID
            group by opn_job_desc,OPN_VALUE
 
i get the o/p as follows
transport                 sload      opn_job_desc            opn_value
12900.396              1997          JOB A                        251
 
but i need column  named attribute 5 from   table named MTL_SYSTEM_ITEMS_FVL P to perform another calculation  perform a left outer
join with    table named  XXNP_OPN_JOBLOG_EST_002   F  as follows
 
select (SUM(case when F.ITEM_UOM = 'Cubic Feet' then
            ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/H.SLURRY_YIELD))ELSE 0 end)*(0.047) +
      sum(case when F.ITEM_UOM =  'Pound' then
           Ceil((ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/DECODE(H.SLURRY_YIELD,0,NULL,H.SLURRY_YIELD)))*94)*F.ITEM_PERCENT/100 )ELSE 0 end)*0.0005)*(j.opn_value/2)transport,
        SUM(case when F.ITEM_UOM = 'Cubic Feet' then
            ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/H.SLURRY_YIELD))end)sload ,
			
			(SUM(case when F.ITEM_UOM = 'Cubic Feet' then
            ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/H.SLURRY_YIELD))ELSE 0 end) +
      sum(case when F.ITEM_UOM =  'Pound' then
           Ceil((ROUND(((H.SLURRY_VOL_ACTUAL * 5.6146)/DECODE(H.SLURRY_YIELD,0,NULL,H.SLURRY_YIELD)))*94)*F.ITEM_PERCENT/100 )ELSE 0 end)*P.ATTRIBUTE5)SBLOAD,
			
			
			
      e.opn_job_desc,J.OPN_VALUE,P.ATTRIBUTE5
     FROM xxnp_opn_joblog_001 E,
 
         XXNP_OPN_JOBLOG_STAGE_002 G,
         XXNP_OPN_JOBLOG_SLURRY_003 H,
 
                 XXNP_OPN_JOBLOG_RES_005 J,
                 XXNP_OPN_JOBLOG_EST_002   F
 
                left outer join
                 MTL_SYSTEM_ITEMS_FVL P on
                  F.INVENTORY_ITEM_ID=P.INVENTORY_ITEM_ID
     where E.OPN_JOB_DESC   = 'KOC/KDC-26/RA-277/6'AND E.MANUAL='N'
         and J.opn_resource_desc='4X4  PICK-UP OR LIGHT VEHICLES'
          AND E.OPN_JOBLOG_001_ID = J.OPN_JOBLOG_001_ID and E.OPN_JOBLOG_001_ID = F.OPN_JOBLOG_001_ID AND
      E.OPN_JOBLOG_001_ID = G.OPN_JOBLOG_001_ID
       AND G.OPN_JOBLOG_006_ID = H.OPN_JOBLOG_006_ID
       AND H.OPN_JOBLOG_007_ID = F.OPN_JOBLOG_007_ID
       group by opn_job_desc,OPN_VALUE,P.ATTRIBUTE5
	   
now i get the following o/p
 
transport        sload      SBLOAD            opn_job_desc               opn_value      ATTRIBUTE5
12900.396       1997                      KOC/KDC-26/RA-277/6       251
 
43.674                        20184        KOC/KDC-26/RA-277/6            251             29
38.152                        23104           KOC/KDC-26/RA-277/6         251             38
208.14175                  135997         KOC/KDC-26/RA-277/6         251             41
111.0675                     84960          KOC/KDC-26/RA-277/6         251             48
83.332                         73040        KOC/KDC-26/RA-277/6           251            55
1549.674                     1481760    KOC/KDC-26/RA-277/6           251             60
23558.609        3994    3994           KOC/KDC-26/RA-277/6         251            94     
 
 
the ideal output should be 
 
transport       sload          sbload          opn_job_desc     opn_value                  
12900.396    1997          2024          KOC/KDC-26/RA-277/6  251                
 
 
 
can anyone please tell me why ? is there any other way to perform a join without affecting the  transport and sload columns
 
 



kindly help

thanking in advance
Edited by: makdutakdu on Feb 3, 2010 10:02 AM

Edited by: makdutakdu on Feb 3, 2010 10:06 AM

Edited by: makdutakdu on Feb 3, 2010 10:14 AM

Edited by: makdutakdu on Feb 3, 2010 10:58 AM

Edited by: makdutakdu on Feb 3, 2010 10:58 AM

Edited by: makdutakdu on Feb 3, 2010 1:01 PM

Edited by: makdutakdu on Feb 3, 2010 1:42 PM

Edited by: makdutakdu on Feb 3, 2010 1:43 PM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 4 2010
Added on Feb 3 2010
8 comments
677 views