I've seen other posts that Teradata doesn't support a scalar subquery within the when clause. However I'm struggling with a reasonably efficient replacement (where I don't have to query for the opposite condition) for this logic:
create table new (Sosec, Beg_date_dttm, CI) as (select sosec, beg_date_dttm, case when a.Beg_date_dttm = (select min(b.Beg_date_dttm) from src b where a.sosec = b.sosec and not exists( select 1 from tgt as c where c.sosec = a.sosec) ) then 2 else 0 end from src a ) WITH DATA;
So if the record doesn't have the earliest beginning date for that sosec on src table, or if that sosec is on the tgt table, then CI should have a value of 0.
Thanks for any suggestions. Be specific though, as I'm not an experienced SQL programmer!