Reputation: 189
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
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