Home » SQL & PL/SQL » SQL & PL/SQL » Alternative Logic for Performance Enhancement!!!
Alternative Logic for Performance Enhancement!!! [message #213352] Wed, 10 January 2007 07:20 Go to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi All,

I have following view created on three base tables involving UNION.

I know this might be bit strange but if you can show better other way if possible.

View looks like
Item , Level, Operation Seq , Dept Class, Dept , Days, Total Offset Days

126157, 0, 9999, MFG,   PROC,  0, 0
126157, 0, 200, SGP,  SGP,   5, 0
126157, 0, 190, MFG,   QA,   1, 0
.
.
126157, 0, 140, MFG,  LAB,   0, 0
126157, 0, 130, MFG,  QA,   1, 0
126157, 0, 120, MFG,  QA,   0, 0
.
.
.
126157, 0, 20, PMD,  GS1,   2, 0
126157, 0, 10, MFG,  PROC,  0, 0



at first in the Total Offset Days will be 0 i need to calculate the total offset days based on order of operation Seq that 9999...10 Start with 10 and end with 9999.

It is for particular level i.e here 0. If consecutive dept comes make other dept days to 0

e.g.

Original Data
ABC , 0 , 130 , MFG, QA , 1, 0
ABC , 0 , 140 , MFG, QA , 1, 0

Make it to 
ABC , 0 , 130 , MFG, QA , 1, 0
ABC , 0 , 140 , MFG, QA , ==> 0 ( it was 1 because 2 consecutive QA made it to 0) , 0

after checking all the record in view calculate total offset day for particular Level.

Original Data before 
126157, 0, 9999, MFG,   PROC,  0, 0
126157, 0, 200, SGP,  SGP,   5, 0
126157, 0, 190, MFG,   QA,   1, 0

Start with Operation Seq 999

After calculation data will look like below
126157, 0, 9999, MFG,   PROC,  0, 0
126157, 0, 200, SGP,  SGP,   5, -5 ( 0-5 = -5)
126157, 0, 190, MFG,   QA,   1, -6 ( -5 -1 = -6).....





so looking for some other way to check consecutive dept and make days to Zero and Cumulative deduction on days column from 9999.

Thanks in advance for any suggestion.

Re: Alternative Logic for Performance Enhancement!!! [message #213429 is a reply to message #213352] Wed, 10 January 2007 13:58 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


curamgph>desc foo;
Name Null? Type
---------------------------------
ITEM VARCHAR2(10)
LVL NUMBER(3)
OSEQ NUMBER(5)
DCLASS VARCHAR2(4)
DEPT VARCHAR2(4)
DYS NUMBER(5)
TOFFDAYS NUMBER(5)



curamgph>select * from foo;

ITEM LVL OSEQ DCLA DEPT DYS TOFFDAYS
---------- ---------- ---------- ---- ---- ---------- ----------
126157 0 9999 MFG PROC 0 0
126157 0 200 SGP SGP 5 0
126157 0 190 MFG QA 1 0
126157 0 140 MFG LAB 0 0
126157 0 130 MFG QA 1 0
126157 0 120 MFG QA 0 0
126157 0 20 PMD GS1 2 0
126157 0 10 MFG PROC 0 0
ABC 0 130 MFG QA 1 0
ABC 0 140 MFG QA 1 0

10 rows selected.




Quote:
select Item , Lvl , OSeq , DClass , Dept , Dys ,
sum(case when rn=1 then dys else dys*-1 end )
over ( partition by item order by oseq ) newtoff ,
case when dept=ldpt then 0 else dys end as newdys
from
(
select Item , Lvl , OSeq , DClass , Dept ,
Dys , TOffDays ,
row_number() over ( partition by item
order by oseq desc ) rn ,
lead (dept,1,'XXX') over ( partition by item
order by oseq desc ) ldpt
from foo
)





curamgph>/

ITEM LVL OSEQ DCLA DEPT DYS NEWTOFF NEWDYS
---------- ---------- ---------- ---- ---- ---------- ---------- ----------
126157 0 10 MFG PROC 0 0 0
126157 0 20 PMD GS1 2 -2 2
126157 0 120 MFG QA 0 -2 0
126157 0 130 MFG QA 1 -3 0
126157 0 140 MFG LAB 0 -3 0
126157 0 190 MFG QA 1 -4 1
126157 0 200 SGP SGP 5 -9 5
126157 0 9999 MFG PROC 0 -9 0
ABC 0 130 MFG QA 1 -1 1
ABC 0 140 MFG QA 1 0 0

10 rows selected.

curamgph>



Srini











Re: Alternative Logic for Performance Enhancement!!! [message #213440 is a reply to message #213352] Wed, 10 January 2007 15:31 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member


I guess I goofed up the order by .
Please try this.

Quote:
select Item , Lvl , OSeq , DClass , Dept , newdys ,
sum(case when rn=1 then dys else dys*-1 end )
over ( partition by item order by oseq desc ) newtoff
from (
select Item , Lvl , OSeq , DClass , Dept ,
Dys , TOffDays ,
row_number() over ( partition by item
order by oseq desc ) rn ,
case when lag (dept,1,'XXX') over ( partition by item
order by oseq desc ) = dept then 0 else dys end as newdys
from foo
)

ITEM LVL OSEQ DCLA DEPT NEWDYS NEWTOFF
---------- ---------- ---------- ---- ---- ---------- ----------
126157 0 9999 MFG PROC 0 0
126157 0 200 SGP SGP 5 -5
126157 0 190 MFG QA 1 -6
126157 0 140 MFG LAB 0 -6
126157 0 130 MFG QA 1 -7
126157 0 120 MFG QA 0 -7
126157 0 20 PMD GS1 2 -9
126157 0 10 MFG PROC 0 -9
ABC 0 140 MFG QA 1 1
ABC 0 130 MFG QA 0 0

10 rows selected.



OR
This one


Quote:
select Item , Lvl , OSeq , DClass , Dept ,
newdys, newtoff
from foo
model return all rows
main grr
partition by ( item )
dimension by (row_number() over
(partition by item order by oseq desc) as drn)
measures (lvl,dclass,dept,dys,toffdays , oseq,
0 as newtoff , 0 as newdys,
row_number() over (partition by item order by oseq desc) as rn
)
ignore nav rules
(
newtoff[any] order by oseq desc = newtoff[cv()-1]+
case when rn[cv()] =1 then
dys[cv()] else dys[cv()]*-1 end ,
newdys[any ]=case when dept[cv()]=dept[cv()-1]
then 0 else dys[cv()] end
)
/

ITEM LVL OSEQ DCLA DEPT NEWDYS NEWTOFF
---------- ---------- ---------- ---- ---- ---------- ----------
126157 0 9999 MFG PROC 0 0
126157 0 200 SGP SGP 5 -5
126157 0 190 MFG QA 1 -6
126157 0 140 MFG LAB 0 -6
126157 0 130 MFG QA 1 -7
126157 0 120 MFG QA 0 -7
126157 0 20 PMD GS1 2 -9
126157 0 10 MFG PROC 0 -9
ABC 0 140 MFG QA 1 1
ABC 0 130 MFG QA 0 0

10 rows selected.



Srini




Re: Alternative Logic for Performance Enhancement!!! [message #213567 is a reply to message #213352] Thu, 11 January 2007 05:12 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hey Thanks Srini...

let me try this
Re: Alternative Logic for Performance Enhancement!!! [message #213601 is a reply to message #213440] Thu, 11 January 2007 07:20 Go to previous messageGo to next message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Hi Srini,

Your query works...but i forgot to mention about group_id,alloy and planner_code

102618,0,9999,MFG,PROC,0,0,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,180,SGP,SGP,5,-5,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,170,MFG,QA,1,-6,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,160,MFG,LAB,0,-6,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,150,MFG,TRP,1,-7,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,140,MFG,QA,1,-8,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,130,MFG,QA,1,-9,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,120,CD,WP,5,-14,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,110,CD,BA,2,-16,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,100,MFG,TRP,1,-17,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,90,MFG,QA,0,-17,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,80,MFG,QA,0,-17,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,70,MFG,QA,0,-17,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,60,MM,MW2,3,-20,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,50,MM,MW1,0,-20,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,40,MM,M2M,0,-20,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,30,MM,M2M,3,-23,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,20,MFG,PROC,0,-23,517919,BRIGHTRAY alloy C,WIRE ROD
102618,0,10,MFG,PROC,0,-23,517919,BRIGHTRAY alloy C,WIRE ROD


so the cumilative deduction should be done on group of group_id,alloy,plan_level and planner_code...i am also trying to use partition by as i have never used it.....

if you throw some idea about it.......

Re: Alternative Logic for Performance Enhancement!!! [message #213604 is a reply to message #213352] Thu, 11 January 2007 07:25 Go to previous message
prashant_pathak
Messages: 263
Registered: February 2006
Location: California,US
Senior Member
Got it...

SELECT ROUTING_ITEM,
	   GROUP_ID,
	   ALLOY,
	   PLANNER_CODE,
	   PLAN_LEVEL,
       OPERATIONSEQ,
       DEPTCLASS,
       DEPT,
       newDAYS,
       SUM(CASE 
             WHEN RN = 1 THEN DAYS
             ELSE DAYS * - 1
           END) OVER(PARTITION BY ROUTING_ITEM,GROUP_ID,ALLOY,PLANNER_CODE,PLAN_LEVEL ORDER BY OPERATIONSEQ DESC) NEWTOFF
FROM   (SELECT ROUTING_ITEM,
   		   	   GROUP_ID,
			   ALLOY,
	   		   PLANNER_CODE,
               PLAN_LEVEL,
               OPERATIONSEQ,
               DEPTCLASS,
               DEPT,
               DAYS,
               TOTALOFFSETDAYS,
               ROW_NUMBER() OVER(PARTITION BY ROUTING_ITEM,GROUP_ID,ALLOY,PLANNER_CODE,PLAN_LEVEL ORDER BY OPERATIONSEQ DESC) RN,
               CASE 
                 WHEN LAG(DEPT,1,'XXX') OVER(PARTITION BY ROUTING_ITEM ORDER BY OPERATIONSEQ DESC) = DEPT THEN 0
                 ELSE DAYS
               END AS NEWDAYS
        FROM   SMCBOM_BOM_OPERATION_VIEW )
Previous Topic: how to call Unix executable from sql plus
Next Topic: Oracle Function to create .CSV file
Goto Forum:
  


Current Time: Thu Dec 08 06:02:08 CST 2016

Total time taken to generate the page: 0.30394 seconds