select within case logic


select within case logic

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!