Using a case expression in an inner join

Question

I've tried to re-write this query several ways and I keep getting different errors. Basically, I'm trying to join on option A or B based on whether the "when" portion of the case statement is true. When I don't use the case stmt, it works fine.

The reason for the case is that we have a downtime on Sundays from 2a-8a. So, I need to account for that time and subtract it from my delivery_time.

This is the error I'm getting:

ORA-00905: missing keyword 00905. 00000 - "missing keyword"

Here is the query that's not working:

SELECT distinct minutes, COUNT(ct.lni) OVER (PARTITION BY minutes) lni_count
FROM card_track ct
INNER JOIN (
  select '00) -1000000->-6001' minutes, -1000000 rng_start, -6001 rng_stop from dual
union (select '01a) -6000->-5001' minutes, -6000 rng_start, -5001 rng_stop from dual) 
union (select '01b) -5000->-4001' minutes, -5000 rng_start, -4001 rng_stop from dual) 
union (select '01c) -4000->-3001' minutes, -4000 rng_start, -3001 rng_stop from dual) 
union (select '01d) -3000->-2501' minutes, -3000 rng_start, -2501 rng_stop from dual) 
union (select '01e) -2500->-2001' minutes, -2500 rng_start, -2001 rng_stop from dual) 
union (select '01f) -2000->-1001' minutes, -2000 rng_start, -1001 rng_stop from dual) 
union (select '02) -1000->-501' minutes, -1000 rng_start, -501 rng_stop from dual) 
union (select '03) -500->-201' minutes, -500 rng_start, -201 rng_stop from dual) 
union (select '04) -200->-101' minutes, -200 rng_start, -101 rng_stop from dual) 
union (select '05) -100->0' minutes, -100 rng_start, 0 rng_stop from dual) 
union (select '06) 1->50' minutes, 1 rng_start, 50 rng_stop from dual) 
union (select '07) 51->100' minutes, 51 rng_start, 100 rng_stop from dual) 
union (select '08) 101->150' minutes, 101 rng_start, 150 rng_stop from dual) 
union (select '09) 151->200' minutes, 151 rng_start, 200 rng_stop from dual) 
union (select '10) 201->250' minutes, 201 rng_start, 250 rng_stop from dual) 
union (select '11) 251->300' minutes, 251 rng_start, 300 rng_stop from dual) 
union (select '12) 301->350' minutes, 301 rng_start, 350 rng_stop from dual) 
union (select '13) 351->400' minutes, 351 rng_start, 400 rng_stop from dual) 
union (select '14) 400->500' minutes, 401 rng_start, 500 rng_stop from dual) 
union (select '15) 500->600' minutes, 501 rng_start, 600 rng_stop from dual) 
union (select '601+' minutes, 601 rng_start, 1000000 rng_stop from dual)
) ON 
case
  when ((to_char(ct.delivery_time,'d')=1) 
     and (to_char(ct.delivery_time,'HH24')>=8 ) 
     and ((to_char(ct.workflow_time,'d')=1) 
     and (to_char(ct.workflow_time,'HH24')<2) 
         or (to_char(ct.workflow_time,'d')=7) ))
then 
  to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999') 
   BETWEEN nvl(rng_start, to_char((ct.delivery_time-(6/24)-ct.la_promote_time)*24*60,'999999')) 
   AND nvl(rng_stop, to_char((ct.delivery_time-(6/24)-ct.la_promote_time)*24*60,'999999'))
else 
  to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999') 
   BETWEEN nvl(rng_start, to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999')) 
   AND nvl(rng_stop, to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999'))
end
where trunc(ct.delivery_time) = trunc(to_date('06-MAR-16'))
and ct.la_promote_time is not null
and ct.delivery_time is not null
and ct.card_status_no in (2,3)
and batch_type in (0,1)
ORDER BY minutes;

Answer 1

Like Kordirko said, your ON clause can much simpler.

ON trunc((ct.delivery_time-ct.la_promote_time)*24*60) BETWEEN rng_start AND rng_stop /* you are comparing integers, no need to covert to strings */

WHERE etc.

But is that what you want?

Answer 2

I expect your issue is with not providing an alias for the subquery.

SELECT distinct minutes, COUNT(ct.lni) OVER (PARTITION BY minutes) lni_count
FROM card_track ct
INNER JOIN (      
select '00) -1000000->-6001' minutes, -1000000 rng_start, -6001 rng_stop from dual
union (select '01a) -6000->-5001' minutes, -6000 rng_start, -5001 rng_stop from dual) 
union (select '01b) -5000->-4001' minutes, -5000 rng_start, -4001 rng_stop from dual) 
union (select '01c) -4000->-3001' minutes, -4000 rng_start, -3001 rng_stop from dual) 
union (select '01d) -3000->-2501' minutes, -3000 rng_start, -2501 rng_stop from dual) 
union (select '01e) -2500->-2001' minutes, -2500 rng_start, -2001 rng_stop from dual) 
union (select '01f) -2000->-1001' minutes, -2000 rng_start, -1001 rng_stop from dual) 
union (select '02) -1000->-501' minutes, -1000 rng_start, -501 rng_stop from dual) 
union (select '03) -500->-201' minutes, -500 rng_start, -201 rng_stop from dual) 
union (select '04) -200->-101' minutes, -200 rng_start, -101 rng_stop from dual) 
union (select '05) -100->0' minutes, -100 rng_start, 0 rng_stop from dual) 
union (select '06) 1->50' minutes, 1 rng_start, 50 rng_stop from dual) 
union (select '07) 51->100' minutes, 51 rng_start, 100 rng_stop from dual) 
union (select '08) 101->150' minutes, 101 rng_start, 150 rng_stop from dual) 
union (select '09) 151->200' minutes, 151 rng_start, 200 rng_stop from dual) 
union (select '10) 201->250' minutes, 201 rng_start, 250 rng_stop from dual) 
union (select '11) 251->300' minutes, 251 rng_start, 300 rng_stop from dual) 
union (select '12) 301->350' minutes, 301 rng_start, 350 rng_stop from dual) 
union (select '13) 351->400' minutes, 351 rng_start, 400 rng_stop from dual) 
union (select '14) 400->500' minutes, 401 rng_start, 500 rng_stop from dual) 
union (select '15) 500->600' minutes, 501 rng_start, 600 rng_stop from dual) 
union (select '601+' minutes, 601 rng_start, 1000000 rng_stop from dual)
) x ON -- Add the alias here before the ON 
case
  when ((to_char(ct.delivery_time,'d')=1) 
     and (to_char(ct.delivery_time,'HH24')>=8 ) 
     and ((to_char(ct.workflow_time,'d')=1) 
     and (to_char(ct.workflow_time,'HH24')<2) 
         or (to_char(ct.workflow_time,'d')=7) ))
then 
  to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999') 
   BETWEEN nvl(rng_start, to_char((ct.delivery_time-(6/24)-ct.la_promote_time)*24*60,'999999')) 
   AND nvl(rng_stop, to_char((ct.delivery_time-(6/24)-ct.la_promote_time)*24*60,'999999'))
else 
  to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999') 
   BETWEEN nvl(rng_start, to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999')) 
   AND nvl(rng_stop, to_char((ct.delivery_time-ct.la_promote_time)*24*60,'999999'))
end
where trunc(ct.delivery_time) = trunc(to_date('06-MAR-16'))
and ct.la_promote_time is not null
and ct.delivery_time is not null
and ct.card_status_no in (2,3)
and batch_type in (0,1)
ORDER BY minutes;