title: How do I coalesce a Time field in Teradata?
body: <p>I'm trying to run the following piece of code:</p>
  
  <pre><code>SELECT a.job_name
       , coalesce(b.target_time, cast('08:00:00' as time(2))) sla_time
  
  FROM ud812.slarpt_job_level_info a
  left outer join ( 
     select job_name, target_time
     from ud812.slarpt_job_target_times 
     qualify row_number() over (partition by job_name 
                                order by established_date desc) = 1
     ) b
  on (a.job_name = b.job_name)
  where  a.display_on_sla_report = 'Y' 
     and a.job_type = 'LD'
     and a.decom_date is null
  </code></pre>
  
  <p>In doing so, I get the error "Select Failed 3800:  Datatype Mismatch in THEN/ELSE expression.
  This relates to my use of coalesce.</p>
  
  <p>When I check my data types:  select type(target_time) from ud812.slarpt_job_target_times qualify...  I get time(2).
  I have tried several things to adjust my code.</p>
  
  <p>These include:  Using a different data types ex. time(6).
  I have even tried the following, but the type for sla_time comes back as integer.</p>
  
  <pre><code>SELECT a.job_name
        , cast(coalesce(b.target_time, '08:00:00') as time(2)) sla_time
  
  FROM ud812.slarpt_job_level_info a
  left outer join ( 
     select job_name, cast(target_time as char(8)) as target_time
     from ud812.slarpt_job_target_times 
     qualify row_number() over (partition by job_name 
                                order by established_date desc) = 1
     ) b
  on (a.job_name = b.job_name)
  where  a.display_on_sla_report = 'Y' 
     and a.job_type = 'LD'
     and a.decom_date is null
  </code></pre>
  
  <p>In the end, I"m trying to establish a default time with each job name of 8 am.
  Ideas?</p>
  
  <p>I have also tried the following , but when I type() the column it returns an integer, not time(2).</p>
  
  <pre><code>SELECT a.job_name
       , cast(coalesce(b.target_time, default_time) as time(2)) sla_time
  FROM ( 
     select job_name, '08:00:00' as default_time
     from ud812.slarpt_job_level_info
     where  a.display_on_sla_report = 'Y' 
       and a.job_type = 'LD'
       and a.decom_date is null
     ) a
  left outer join ( 
     select job_name, target_time
     from ud812.slarpt_job_target_times 
     qualify row_number() over (partition by job_name 
                                order by established_date desc) = 1
     ) b
  on (a.job_name = b.job_name)
  </code></pre>