CASE When Then returns unexpected result

CASE When Then returns unexpected result

• deen

  I did the isnull thing and it works.

  select Duration, Machine, AutoCause, Reason1, Process_Failure from Downtimeview
  Where Machine = 'MHFN-300-004-030: Stretch Wrapper 4'
  and Autocause = 'Stretch Wrapper Mtr OL'
  and Duration > 10

  Duration Machine AutoCause Reason1 Process_Failure
  ------------------------ -------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------------
  21.066668 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  907.48334 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  1140.9166 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  1214.5 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  1683.8167 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  179887.03 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  5633.1001 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  113915.95 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  64.666664 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  11.666667 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  17.683332 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  12.666667 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1
  10.25 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1

  (13 row(s) affected)

  THANK YOU

  
  
  

• tssweb

  Hi,

  I'm wondering if it might be related to the NULL value for Event_Reason_Name. Please see if using ISNULL fixes it, e.g.,

  WHEN TED.Duration > 10 AND rtrim(ltrim(ISNULL(ER1.Event_Reason_Name,'xyz'))) like '%ygiene/Cleanu%'
  THEN 0

  You will have to put this in all your LTRIM entries that have a value that may be NULL. (Maybe you will want to use an empty string, '', instead of 'xyz'.)

  Otherwise it appears that with your data, it should fail all tests, and you should end up with "ELSE 1".

  NULL values are rather tricky. Using ISNULL gives you better control over them.

  Dan

  
  

• Smacker

  Well, I copied your CASE statement and I also do not understand why you are getting zero; it might be worh considering that the case statement is not the source of your problem:

  declare @ted table (duration integer)
  declare @pu1 table (pu_desc varchar(40))
  declare @tef table (TEFault_name varchar(30))
  declare @er1 table (Event_Reason_Name varchar(30))

  insert into @ted values (720)
  insert into @pu1 values ('MHFN-300-004-030: Stretch Wrapper 4')
  insert into @tef values ('Stretch Wrapper Mtr OL')
  insert into @er1 values (null)

  select ted.duration,
  pu1.pu_desc,
  tef.tefault_name,
  er1.event_reason_name,
  CASE WHEN TED.Duration < 10
  THEN 0
  WHEN TED.Duration > 10 AND PU1.PU_Desc like 'Sch%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Speed'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Tracking'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%State'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%sys:'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%match'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%LkDetec%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%ealerCon%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%apper Hoppe%'--
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%FullCaseCon%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%locke%' -- blocked
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%tarve%' --starved
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%ismatc%'--bar code mismatch--
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%hute Below Low Leve%'--Chute Below Low Level
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like 'Cycle Pause%'--Cycle Pause
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto%'--non terminating lang
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto'--terminating lang
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Produc%'--Out of Product
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Bin Ful%'--Sheet Frame Bin Full)
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%locke%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%ygiene/Cleanu%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%aterial Chang%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%pecia%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%tarve%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%upply Networ%'
  THEN 0
  ELSE 1 END AS Process_Failure,

  CASE WHEN TED.Duration < 10
  THEN 0
  WHEN TED.Duration > 10 AND PU1.PU_Desc like 'Sch%'
  THEN 2
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Speed'
  THEN 3
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Tracking'
  THEN 4
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%State'
  THEN 5
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%sys:'
  THEN 6
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%match'
  THEN 7
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%LkDetec%'
  THEN 8
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%ealerCon%'
  THEN 9
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%apper Hoppe%'--
  THEN 10
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%FullCaseCon%'
  THEN 11
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%locke%' -- blocked
  THEN 12
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%tarve%' --starved
  THEN 13
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%ismatc%'--bar code mismatch--
  THEN 14
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%hute Below Low Leve%'--Chute Below Low Level
  THEN 15
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like 'Cycle Pause%'--Cycle Pause
  THEN 16
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto%'--non terminating lang
  THEN 17
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto'--terminating lang
  THEN 18
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Produc%'--Out of Product
  THEN 19
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Bin Ful%'--Sheet Frame Bin Full)
  THEN 20
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%locke%'
  THEN 21
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%ygiene/Cleanu%'
  THEN 22
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%aterial Chang%'
  THEN 23
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%pecia%'
  THEN 24
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%tarve%'
  THEN 25
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%upply Networ%'
  THEN 26
  ELSE 1 END AS Modified_Process_Failure

  from @ted ted
  cross join @pu1 pu1
  cross join @tef tef
  cross join @er1 er1

  -- duration pu_desc tefault_name event_reason_name Process_Failure Modified_Process_Failure
  -- ----------- ---------------------------------------- ------------------------------ ------------------------------ --------------- ------------------------
  -- 720 MHFN-300-004-030: Stretch Wrapper 4 Stretch Wrapper Mtr OL NULL 1 1

  
  

• Lappis

  Great! (Especially since I didn't know what else to suggest.) ;-)
  

• xRuntime

  For what it's worth.. Here's the entire view. I certainly appreciate anyone taking time out to look at this.

  SET QUOTED_IDENTIFIER OFF
  GO
  SET ANSI_NULLS ON
  GO

  ALTER view DowntimeView as

  SELECT SP.Value as Site,
  TED.Start_time,
  TED.End_Time,
  TED.Duration,
  TED.Uptime,
  PL.PL_Desc as Line,
  PU1.PU_Desc AS Machine,
  PU2.PU_Desc as Area,
  TEF.TEFault_Name as AutoCause,
  ER1.Event_Reason_Name AS Reason1,
  ER2.Event_Reason_Name AS Reason2,
  ER3.Event_Reason_Name AS Reason3,
  ER4.Event_Reason_Name AS Reason4,
  CASE WHEN TED.Duration < 10
  THEN 0
  WHEN TED.Duration > 10 AND PU1.PU_Desc like 'Sch%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Speed'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%Tracking'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%State'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%sys:'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%match'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%LkDetec%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%ealerCon%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%apper Hoppe%'--
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(PU1.PU_Desc)) like '%FullCaseCon%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%locke%' -- blocked
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%tarve%' --starved
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%ismatc%'--bar code mismatch--
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%hute Below Low Leve%'--Chute Below Low Level
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like 'Cycle Pause%'--Cycle Pause
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto%'--non terminating lang
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Not In Auto'--terminating lang
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Produc%'--Out of Product
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(TEF.TEFault_Name)) like '%Bin Ful%'--Sheet Frame Bin Full)
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%locke%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%ygiene/Cleanu%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%aterial Chang%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%pecia%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%tarve%'
  THEN 0
  WHEN TED.Duration > 10 AND rtrim(ltrim(ER1.Event_Reason_Name)) like '%upply Networ%'
  THEN 0
  ELSE 1 END AS Process_Failure,
  CASE WHEN TED.Duration > 10 AND ER1.Event_Reason_Name = 'Mechanical'
  OR TED.Duration > 10 AND ER1.Event_Reason_Name = 'Electrical'
  THEN 1
  ELSE 0 END AS Breakdown,
  CASE WHEN TED.Duration <= 10 AND ER1.Event_Reason_Name = 'Mechanical'
  OR TED.Duration <= 10 AND ER1.Event_Reason_Name = 'Electrical'
  OR TED.Duration <= 10 AND ER1.Event_Reason_Name = 'Operational'
  OR TED.Duration <= 10 AND ER1.Event_Reason_Name is null
  THEN 1
  ELSE 0 END AS Minor_Stop,
  0 AS Material_Incident,
  CASE WHEN ER2.Event_Reason_Name = 'Planned Shutdown'
  THEN 1
  ELSE 0 END AS Planned_Shutdown,
  CASE WHEN ER2.Event_Reason_Name = 'MPS'
  OR ER2.Event_Reason_Name = 'Planned Downtime'
  THEN 1
  ELSE 0 END AS MPS,
  CASE WHEN ER2.Event_Reason_Name = 'Planned Downtime'
  THEN 1
  ELSE 0 END AS Planned_Downtime,
  CASE WHEN TEF.TEFault_Name Like 'Blocked%'
  OR TEF.TEFault_Name Like '_Blocked%'
  THEN 1
  ELSE 0 END AS Blocked,
  CASE WHEN TEF.TEFault_Name Like 'Starved%'
  OR TEF.TEFault_Name Like '_Starved%'
  THEN 1
  ELSE 0 END AS Starved,
  CASE WHEN TED.Duration > 180 AND ER1.Event_Reason_Name = 'Mechanical'
  OR TED.Duration >180 AND ER1.Event_Reason_Name = 'Electrical'
  THEN 1
  ELSE 0 END AS Major_Breakdown,
  TED.TEDet_Id AS Comment_ID,
  Users.Username,
  TED.PU_Id AS Machine_ID,
  CS.CrewSched_ID AS CrewSched_ID
  
  FROM Timed_Event_Details TED WITH (NOLOCK) INNER JOIN
  Site_Parameters SP WITH (NOLOCK) ON SP.Parm_ID = 12 INNER JOIN
  Users WITH (NOLOCK) ON TED.User_Id = Users.User_Id LEFT OUTER JOIN
  Local_PG_CrewSched_ID CS WITH (NOLOCK) ON TED.PU_Id = CS.Machine_ID LEFT OUTER JOIN
  Prod_Units PU1 WITH (NOLOCK) ON TED.PU_Id = PU1.PU_Id LEFT OUTER JOIN
  Prod_Lines PL WITH (NOLOCK) ON PU1.PL_Id = PL.PL_Id LEFT OUTER JOIN
  Prod_Units PU2 WITH (NOLOCK) ON TED.Source_PU_Id = PU2.PU_Id LEFT OUTER JOIN
  Timed_Event_Fault TEF WITH (NOLOCK) ON TED.TEFault_Id = TEF.TEFault_Id LEFT OUTER JOIN
  Event_Reasons ER1 WITH (NOLOCK) ON TED.Reason_Level1 = ER1.Event_Reason_Id LEFT OUTER JOIN
  Event_Reasons ER2 WITH (NOLOCK) ON TED.Reason_Level2 = ER2.Event_Reason_Id LEFT OUTER JOIN
  Event_Reasons ER3 WITH (NOLOCK) ON TED.Reason_Level3 = ER3.Event_Reason_Id LEFT OUTER JOIN
  Event_Reasons ER4 WITH (NOLOCK) ON TED.Reason_Level4 = ER4.Event_Reason_Id
  

  
  
  
• CASE When Then returns unexpected result