CODEWITHSUNDEEP
sqloracle-database
Worst SQL Noob
Worst SQL Noob

Reputation: 189

Using Case When Clause in Where Clause

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!

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'))<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'))<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'))<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

Upvotes: 0

Views: 96

Answers (1)

unleashed
unleashed

Reputation: 771

You seem to be asking about whether or not the use of CASE .. WHEN in the where clause is appropriate. What you have is okay. Gordon is refering to using ANSI style joins. I've re-written with ANSI joins (FROM clause) as an example.

    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'))<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
        inner join fd_stg.trn_plan_resource_tra b on (a.trn_plan_id = b.trn_plan_id and b.role = '003')
        inner join fd_dw.date_dim c on (a.trn_plan_date = c.calendar_date 
                                          and (c.last_month='Y'
                                            or c.month_to_date='Y' 
                                            or c.last_week='Y') )
        inner join fd_dw.route_dim d on (case when to_number(to_char(a.cutoff_datetime,'HH24'))<20 then 'AM' else 'PM' end=d.ampm_shift
                                            and a.trn_plan_date = d.route_date
                                            and a.trn_zone_code = d.zone)
    where
            (a.trn_region_code = 'Depot' or a.trn_zone_code in('970','971'))
        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'))<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

Upvotes: 2

Related Questions