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!

help with joins

makdutakduFeb 2 2010 — edited Feb 9 2010
hi

i am giving the create table statements 
CREATE TABLE XXNP_OPN_JOBLOG_001
(
  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
(
  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_STAGE_002
(
  OPN_JOBLOG_006_ID  NUMBER,
  OPN_JOBLOG_001_ID  NUMBER,
  JOB_ID             NUMBER,
  ORG_ID             NUMBER(15)
  
)


CREATE TABLE XXNP_OPN_JOBLOG_SLURRY_003
(
  OPN_JOBLOG_007_ID    NUMBER,
  OPN_JOBLOG_006_ID    NUMBER,
  SLURRY_WEIGHT        NUMBER,
  SLURRY_YIELD         NUMBER,
  SLURRY_VOL_ACTUAL    NUMBER,
  OPN_JOBLOG_001_ID    NUMBER
 
)



CREATE TABLE XXNP_OPN_JOBLOG_RES_005
(
  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
(
  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 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_EST_002   F,
          XXNP_OPN_JOBLOG_STAGE_002 G,
          XXNP_OPN_JOBLOG_SLURRY_003 H,
		  XXNP_OPN_JOBLOG_RES_005 J
      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
        AND G.OPN_JOBLOG_006_ID = H.OPN_JOBLOG_006_ID
        AND H.OPN_JOBLOG_007_ID = F.OPN_JOBLOG_007_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 e.opn_job_desc,J.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

regards
oracleuser

Edited by: makdutakdu on Feb 2, 2010 2:45 PM

Edited by: makdutakdu on Feb 2, 2010 3:04 PM

Edited by: makdutakdu on Feb 2, 2010 3:04 PM

Edited by: makdutakdu on Feb 2, 2010 3:26 PM

Edited by: makdutakdu on Feb 2, 2010 3:28 PM

Edited by: makdutakdu on Feb 2, 2010 8:29 PM

Edited by: makdutakdu on Feb 3, 2010 8:33 AM

Edited by: makdutakdu on Feb 3, 2010 9:07 AM

Edited by: makdutakdu on Feb 4, 2010 2:48 PM

Edited by: makdutakdu on Feb 9, 2010 12:51 PM
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 6 2010
Added on Feb 2 2010
12 comments
1,097 views