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.


Code:

create or replace
VIEW Unit_Hours_Qry (ToolGroupCap) AS (

SELECT t1.*,
(CASE WHEN t1****nSize < 1 THEN t1.FixedTime + t1.VarTime
ELSE t1****nSize * 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****nSize 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,
DECODE(CT1, NULL,
(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
);

See this AskTom thread.

Thank You for your help