Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2004

    Unanswered: View with a parameter

    Hi folks,

    I have to bring a load of queries from Access over to Oracle and Im trying to simply put them in as Views.
    One in particular needs a parameter passed in but I get an error about not being allowed to use bind variables.

    Does anyone know a way to do this?
    Your help as always is greatly appreciated.


    create or replace
    VIEW Unit_Hours_Qry (ToolGroupCap) AS (

    SELECT t1.*,
    (CASE WHEN t1.RunSize < 1 THEN t1.FixedTime + t1.VarTime
    ELSE t1.RunSize * t1.TimePerWafer END) AS PCT
    FROM (
    SELECT Unit_Hours.STEP step,
    Unit_Hours.STAGE stage, Unit_Hours.PRODUCT product,
    Unit_Hours.PERIOD period, Unit_Hours.CT1 ct1,
    Unit_Hours.CT2 ct2, Unit_Hours.CT3 ct3,
    Unit_Hours.CT4 ct4, Unit_Hours.CT5 ct5,
    Unit_Hours.CT6 ct6, Unit_Hours.CT7 ct7,
    Unit_Hours.CT8 ct8, Unit_Hours.CT9 ct9,
    Unit_Hours.CT10 ct10, Unit_Hours.CT11 ct11,
    Unit_Hours.CT12 ct12, Unit_Hours.TimeStudyDate TimeStudyDate,
    Unit_Hours.FixedTime FixedTime, Unit_Hours.VarTime VarTime,
    Unit_Hours.Rework Rework, Unit_Hours.RunSize RunSize,
    (CASE :ToolGroupCap WHEN 1 THEN Tool_Info.ToolName
    WHEN 2 THEN Tool_Info.ToolFam
    WHEN 3 THEN Tool_Info.ToolGroup
    ELSE Tool_Info.ToolName END) AS ToolName,
    Unit_Hours.CreateDate CreateDate, Unit_Hours.Comments Comments,
    (CASE WHEN RunSize>1 THEN (FixedTime + VarTime * RunSize)/RunSize
    ELSE (FixedTime + VarTime)* RunSize END),
    (CASE WHEN RunSize<1.5 THEN CT1
    WHEN RunSize<2.5 THEN CT2
    WHEN RunSize<3.5 THEN CT3
    WHEN RunSize<4.5 THEN CT4
    WHEN RunSize<5.5 THEN CT5
    WHEN RunSize<6.5 THEN CT6
    WHEN RunSize<7.5 THEN CT7
    WHEN RunSize<8.5 THEN CT8
    WHEN RunSize<9.5 THEN CT9
    WHEN RunSize<10.5 THEN CT10
    WHEN RunSize<11.5 THEN CT11
    WHEN RunSize>=11.5 THEN CT12
    ELSE 0 END)
    ) AS TimePerWafer,
    COALESCE(CT1, FixedTime + VarTime, 0) AS TCT,
    --(CASE WHEN RunSize < 1 THEN FixedTime + VarTime
    -- ELSE RunSize * TimePerWafer END) AS PCT,
    Unit_Hours.Actual_RunSize Actual_Runsize,
    Tool_Info.Staffing_Area Staffing_Area,
    Unit_Hours.Sampling Sampling
    FROM Tool_Info
    JOIN Unit_Hours
    ON Tool_Info.ToolName = Unit_Hours.ToolName) t1

  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

  3. #3
    Join Date
    Jul 2004


    Thank You for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts