CDDA - National designation type

SELECT * FROM (SELECT CAST(designated_site.cdda_id AS VARCHAR) site_code,
designation_type.designation_type_code des_code,
designation_type.designation_type_name cdda_designation_national_language,
designation_type.designation_type_name_english designation,
case when designation_type_category in ('A','B','C') then designation_type.national_designation_description + ' (Code ' + designation_type_category + ')'
else
designation_type.national_designation_description 
end description,
designated_site.legal_foundation_year year_established
FROM EUNIS.latest.Natda_designated_site designated_site,
EUNIS.latest.Natda_designation_type designation_type,
[BISE].[latest].[Country_Information] country
WHERE
designation_type.designation_type_code=designated_site.designation_type_code
AND country.country_code = designated_site.cdda_country_code
) as a