title: Using Case When Clause in Where Clause
body: <p>I am using Oracle for my work, and I have following script, I want to join the data, zone number and shift in table a and d; but in my table a I don;t have shift, so I create shift by using cash when statement, but I have to join the created shift with shift in table a, but sql works and give me the data I want to, but I just want to make sure this is the right way to do it!</p>
  
  <pre><code>select distinct
      a.trn_plan_date as route_date,
      next_day(a.trn_plan_date - 1,'Sunday') as route_week,
      a.trn_zone_code as zone,
      case when to_number(to_char(a.cutoff_datetime,'HH24'))&lt;20 then 'AM' else 'PM' end as shift,
      d.ampm_shift,
      max(d.ups_checkin_time) as Ups,
      d.productivity_region,
      'PLANNED_DEPOT_RUNNER' as hour_type,
      24*(a.truck_endtime -  a.truck_dispatchtime)*count(distinct b.trn_resource_id) as hours,
      c.last_week as last_week_flag,
      c.month_to_date as month_to_date_flag,
      c.last_month as last_month_flag
  from
      fd_stg.trn_plan_tra a,
      fd_stg.trn_plan_resource_tra b,
      fd_dw.date_dim c,
      fd_dw.route_dim d
  where
      a.trn_plan_id = b.trn_plan_id
      and a.trn_plan_date = c.calendar_date
      and case when to_number(to_char(a.cutoff_datetime,'HH24'))&lt;20 then 'AM' else 'PM' end=d.ampm_shift
      and a.trn_plan_date = d.route_date
      and a.trn_zone_code = d.zone
      and (c.last_month='Y'
          or c.month_to_date='Y' 
          or c.last_week='Y')       
      and (a.trn_region_code = 'Depot' or a.trn_zone_code in('970','971'))
      and b.role = '003'
      and a.trn_zone_code is not null
  group by
      a.trn_plan_date,
      a.trn_zone_code,
      case when to_number(to_char(a.cutoff_datetime,'HH24'))&lt;20 then 'AM' else 'PM' end,
      d.productivity_region,
      d.ampm_shift,
      a.truck_endtime - a.truck_dispatchtime,
      c.last_week,
      c.month_to_date,
      c.last_month
    Order by 1,3,4
  </code></pre>