Teradata: Datatype mismatch error when adding subquery to join

scify — Thu, 17 Oct 2019 15:18:12 GMT

I have the following piece of code that works fine as a standalone pull:

proc sql;
	connect to teradata (authdomain='LDAP_auth' SERVER= teramed1 mode = teradata);
	create table test as 
	select * from connection to teradata (
                select sub_ed.eci, sub_ed.hh_episode_bgn,count(sub_ed.incurred_dt) as ed_during_ct
                , min(incurred_dt) as ed_during_dt
		from sudd02a.hh_3_ed sub_ed left join sudd02a.hh_1_episodes sub_ep
		on sub_ed.eci = sub_ep.eci and sub_ed.hh_episode_bgn = sub_ep.hh_episode_bgn
		group by 1,2
		where sub_ed.incurred_dt between sub_ed.hh_episode_bgn and sub_ep.hh_episode_end);
quit;

However, when I wrap the select statement into a left join as part of a much larger pull from Teradata

left join (select sub_ed.eci, sub_ed.hh_episode_bgn
		, count(sub_ed.incurred_dt) as ed_during_ct
		, min(incurred_dt) as ed_during_dt
		from sudd02a.hh_3_ed sub_ed left join sudd02a.hh_1_episodes sub_ep
		on sub_ed.eci = sub_ep.eci and sub_ed.hh_episode_bgn = sub_ep.hh_episode_bgn
		group by 1,2
		where sub_ed.incurred_dt between sub_ed.hh_episode_bgn and sub_ep.hh_episode_end) ed2
on ep.eci=ed2.eci and ep.hh_episode_bgn=ed2.hh_episode_bgn

Enterprise Guide throws the error:

ERROR: Teradata prepare: Datatype Mismatch in THEN/ELSE expression.

I can't figure out what might be causing that error. It's not the ON variables: there's a join just above this one that uses the exact same tables and variables, and it works fine.

Re: Teradata: Datatype mismatch error when adding subquery to join

ChrisNZ — Thu, 17 Oct 2019 23:07:20 GMT

It looks like your ed2 variables have a different type.

topic Teradata: Datatype mismatch error when adding subquery to join in SAS Programming

Teradata: Datatype mismatch error when adding subquery to join

Re: Teradata: Datatype mismatch error when adding subquery to join