Hi all,

The followinf is sql I converted to run on oracle from sql server, not too sure about oracle so if anyone can show me how to do it right I'd be extremely greatful.

Thanks.


SELECT
         a.OEE,
         a.UnplannedDT,
         a.PlannedDT,
         (a.ScheduledDT + b.ScheduledDTTime) As "ScheduledDT",
         a.SpeedLossTime,
         a.YieldLossTime,
         a.AnalysisBy,
         a.Scheduled
FROM ( SELECT
             ROUND(SUM((PackedQty - wasteqty) / (OEERate / OEETimeUnit)), 2) AS OEE,
             SUM(UnplannedDT) As "UnplannedDT",
             SUM(PlannedDT) As "PlannedDT",
             SUM(breaks) As "ScheduledDT",
             ROUND(SUM((wasteqty) / (OEERate / OEETimeUnit)), 2) As "YieldLossTime",
             ROUND(SUM(runtime - ((PackedQty) / (OEERate / OEETimeUnit))), 2) As "SpeedLossTime",
             SUM(tblPO.TotalTime) As "Scheduled",
             Shift As "AnalysisBy"
    FROM tblPO,
         tblLines,
         tblProducts,
         staProdCategory,
         staArea,
         staFacilityLocation,
         staBusinessUnit,
         staPlant
    WHERE tblLines.Line = tblPO.Line
     AND (tblProducts.Product = tblPO.Product)
     AND (staProdCategory.ProdCatId = tblProducts.ProdCatId)
     AND (staArea.AreaID = tblLines.AreaID)
     AND (staFacilityLocation.FacilityID = staArea.FacilityID)
     AND (staBusinessUnit.UnitID = staFacilityLocation.UnitID)
     AND (staPlant.PlantID = staBusinessUnit.PlantID)
     AND (tblLines.AnalysisType = 'OEE'
     AND OEERATE > 0
     AND OEETIMEUNIT > 0
     AND tblLines.Line IN ( 'LTHE0016' )
     AND date_ >= '01-Nov-06'
     AND date_ < '08-Nov-06')
    GROUP BY Shift
) a,
    ( SELECT
             SUM(ScheduledDTHours * 60) AS ScheduledDTTime,
             SUM((DECODE(1 , DECODE( 2 ,DECODE( StartTime , NULL , 1 , 0 ) + DECODE( FinishTime , NULL , 1 , 0 ) , 1 , 0 ) ,ScheduledHours * 60,ROUND(( (DECODE(1 , DECODE( 1 ,DECODE(ABS(FinishTime - (SYSDATE) ),0,0, (FinishTime - (SYSDATE) ),1,0 ) , 1 , 0 ) ,(SYSDATE),FinishTime)) - StartTime ) *24*60)) + addtnscheduledhours * 60) - unscheduledhours * 60) AS scheduled,
             Shift As "AnalysisBy"
    FROM staworkpatterns,
         tblLines,
         staArea,
         staFacilityLocation,
         staBusinessUnit,
         staPlant
    WHERE tblLines.Line = staworkpatterns.WorkCentreID
     AND (staArea.AreaID = tblLines.AreaID)
     AND (staFacilityLocation.FacilityID = staArea.FacilityID)
     AND (staBusinessUnit.UnitID = staFacilityLocation.UnitID)
     AND (staPlant.PlantID = staBusinessUnit.PlantID)
     AND (tblLines.AnalysisType = 'OEE'
     AND staworkpatterns.workcentreid IN ( 'LTHE0016' )
     AND staworkpatterns.currentdate >= '01-Nov-06'
     AND staworkpatterns.currentdate < '08-Nov-06')
    GROUP BY Shift
) b
WHERE a.AnalysisBy = b.AnalysisBy
ORDER BY a.Scheduled DESC

brettk_1:
More information is needed, because what you have written would not execute successfully in Oracle. It is difficult to believe that the code you submitted executed successfully in SQL Server. The following information is needed in order to answer your question: What do you mean by in your topic- Subquery as table; sample of what the output should be; schemas of db tables, used in the query.